Re: Bug? 8.0 does not use partial index
От | Palle Girgensohn |
---|---|
Тема | Re: Bug? 8.0 does not use partial index |
Дата | |
Msg-id | 33D183419CC483774A99F5F2@palle.girgensohn.se обсуждение исходный текст |
Ответ на | Re: Bug? 8.0 does not use partial index (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Bug? 8.0 does not use partial index
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Bug? 8.0 does not use partial index (John Hansen <john@geeknet.com.au>) |
Список | pgsql-hackers |
Yes, they are analyzed and vacuumed. How do you mean they look far off? The data in the two db:s where not identical in the example i sent. With identical data in both 7.4.5 and 8.0.0rc5 (both freshly pg_restored and vacuum analyzed), 7.4.5 used the index, and for 8.0.0rc5, when I add the this_group_id=46 *three times*, it decides to use the index. So, this might be a special case, but with more data, expected into the system shortly, the query takes 30 secs on 8.0.0rc5 and 12 ms on 7.4.5. That's a factor of 2000, which is too much for me :( If you want, I can send you the data. orig=# create index foo on group_data(this_group_id) where group_id is null; CREATE INDEX Time: 2240.438 ms kthorig=# vacuum analyze group_data; VACUUM Time: 13222.171 ms kthorig=# explain analyze select * from group_data where group_id is null and this_group_id = 46; QUERY PLAN -------------------------------------------------------------------------------------------------------------------Seq Scanon group_data (cost=0.00..47544.43 rows=114164 width=43) (actual time=114.015..1334.479 rows=4 loops=1) Filter: ((group_id IS NULL) AND (this_group_id = 46))Total runtime: 1334.526 ms (3 rows) Time: 1335.794 ms orig=# explain analyze select * from group_data where group_id is null and this_group_id = 46 and this_group_id = 46; QUERY PLAN ------------------------------------------------------------------------------------------------------------------Seq Scanon group_data (cost=0.00..52953.91 rows=43230 width=43) (actual time=126.061..1344.729 rows=4 loops=1) Filter: ((group_id IS NULL) AND (this_group_id = 46) AND (this_group_id = 46))Total runtime: 1344.777 ms (3 rows) Time: 1345.684 ms orig=# explain analyze select * from group_data where group_id is null and this_group_id = 46 and this_group_id = 46 and this_group_id = 46 and this_group_id = 46; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------Index Scanusing foo on group_data (cost=0.00..11791.58 rows=6199 width=43) (actual time=12.455..12.465 rows=4 loops=1) Index Cond: ((this_group_id = 46) AND (this_group_id = 46) AND (this_group_id = 46) AND (this_group_id = 46)) Filter: (group_id IS NULL)Total runtime: 12.519 ms (4 rows) Time: 13.932 ms orig=# select count(this_group_id) from group_data where this_group_id=46;count --------797426 (1 row) Time: 1843.869 ms orig=# select count(this_group_id) from group_data where this_group_id=46 and group_id is null;count ------- 4 (1 row) Time: 1647.350 ms ====================================================0 same thing on 7.4.5: kth=# create index foo on group_data(this_group_id) where group_id is null; CREATE INDEX kth=# vacuum analyze group_data; \timing VACUUM kth=# \timing Timing is on. kth=# explain analyze select * from group_data where group_id is null and this_group_id = 46; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------Index Scanusing foo on group_data (cost=0.00..40408.72 rows=109317 width=43) (actual time=0.154..0.175 rows=4 loops=1) Index Cond: (this_group_id = 46) Filter: (group_id IS NULL)Total runtime:0.241 ms (4 rows) Time: 2,785 ms kth=# explain analyze select * from group_data where group_id is null and this_group_id = 46 and this_group_id = 46; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------Index Scanusing foo on group_data (cost=0.00..40408.72 rows=109317 width=43) (actual time=0.033..0.054 rows=4 loops=1) Index Cond: (this_group_id = 46) Filter: (group_id IS NULL)Total runtime:0.121 ms (4 rows) Time: 1,607 ms kth=# explain analyze select * from group_data where group_id is null and this_group_id = 46 and this_group_id = 46 and this_group_id = 46 and this_group_id = 46; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------Index Scanusing foo on group_data (cost=0.00..40408.72 rows=109317 width=43) (actual time=0.033..0.055 rows=4 loops=1) Index Cond: (this_group_id = 46) Filter: (group_id IS NULL)Total runtime:0.119 ms (4 rows) Time: 1,702 ms kth=# select count(this_group_id) from group_data where this_group_id=46;count --------797426 (1 row) Time: 1821,433 ms kth=# select count(this_group_id) from group_data where this_group_id=46 and group_id is null;count ------- 4 (1 row) Time: 1,635 ms /Palle --On torsdag, januari 13, 2005 16.33.58 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Palle Girgensohn <girgen@pingpong.net> writes: >> On 7.4.5, it uses the index, but on 8.0rc5, it does not: > > Have you ANALYZEd the 8.0 table lately? Those rowcount estimates look > mighty far off. > > regards, tom lane
В списке pgsql-hackers по дате отправления: