Re: Some issues with planner and query optimization
От | Richard Huxton |
---|---|
Тема | Re: Some issues with planner and query optimization |
Дата | |
Msg-id | 45C73B0E.90503@archonet.com обсуждение исходный текст |
Ответ на | Some issues with planner and query optimization ("Boguk Maxim" <astar@rambler-co.ru>) |
Список | pgsql-general |
Boguk Maxim wrote: > Postgres 8.1 > Sample test table: > (all queries done on fresh vacuumed analyzed table with statistics on > rub_id and news_dtime set to 1000) > (all table in memory and server do not doing anything other) > > media=> \d test_table > Table "public.test_table" > Column | Type | Modifiers > ------------+-----------------------------+----------- > id | integer | > rub_id | integer | > news_id | integer | > news_dtime | timestamp without time zone | > Indexes: > "test_table_pk" UNIQUE, btree (id) > "test_table_main_idx" btree (rub_id, news_dtime) > > media=> select count(*) from test_table; > count > --------- > 5834463 > media=> select count(distinct rub_id) from test_table; > count > ------- > 342 > > Now doing 3 simple query: > > First: > media=> EXPLAIN ANALYZE select * from test_table where rub_id IN (5) > order by news_dtime limit 20; > QUERY > PLAN > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > Limit (cost=0.00..10.73 rows=20 width=20) (actual time=0.018..0.121 > rows=20 loops=1) > -> Index Scan using test_table_main_idx on test_table > (cost=0.00..29758.11 rows=55447 width=20) (actual time=0.014..0.054 > rows=20 loops=1) > Index Cond: (rub_id = 5) > Total runtime: 0.186 ms > > Second (almost same but with rub_id 8): > media=> EXPLAIN ANALYZE select * from test_table where rub_id IN (8) > order by news_dtime limit 20; > QUERY > PLAN > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > - > Limit (cost=0.00..1.98 rows=20 width=20) (actual time=0.019..0.121 > rows=20 loops=1) > -> Index Scan using test_table_main_idx on test_table > (cost=0.00..45976.37 rows=463684 width=20) (actual time=0.014..0.054 > rows=20 loops=1) > Index Cond: (rub_id = 8) > Total runtime: 0.186 ms > > > Now try with rub_id IN (5,8) (I was assumed query will work 2-10 time > longer max... With almost same plan) > But i'm got bad plan/really slow query: > > media=> EXPLAIN ANALYZE select * from test_table where rub_id IN (5,8) > order by news_dtime limit 20; > > QUERY PLAN > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ------------- > Limit (cost=103337.45..103337.50 rows=20 width=20) (actual > time=4437.841..4437.976 rows=20 loops=1) > -> Sort (cost=103337.45..104624.26 rows=514725 width=20) (actual > time=4437.836..4437.873 rows=20 loops=1) > Sort Key: news_dtime > -> Bitmap Heap Scan on test_table (cost=3818.96..54506.92 > rows=514725 width=20) (actual time=82.139..1100.021 rows=515340 loops=1) > Recheck Cond: ((rub_id = 5) OR (rub_id = 8)) > -> BitmapOr (cost=3818.96..3818.96 rows=519131 width=0) > (actual time=80.498..80.498 rows=0 loops=1) > -> Bitmap Index Scan on test_table_main_idx > (cost=0.00..409.06 rows=55447 width=0) (actual time=8.342..8.342 > rows=54959 loops=1) > Index Cond: (rub_id = 5) > -> Bitmap Index Scan on test_table_main_idx > (cost=0.00..3409.89 rows=463684 width=0) (actual time=72.146..72.146 > rows=460381 loops=1) > Index Cond: (rub_id = 8) > Total runtime: 4458.999 ms > (11 rows) > > Ouch.... 25000 slower... > Why planner not try two index scan and merge results... Try: ORDER BY rub_id, news_dtime Does that give it enough of a hint? The problem is you're asking for the 20 oldest regardless of rub_id, so the index isn't as much use as it might be. Perhaps an index on (news_dtime,rub_id) rather than the other way around? -- Richard Huxton Archonet Ltd
В списке pgsql-general по дате отправления: