Bug? 8.0 does not use partial index
От | Palle Girgensohn |
---|---|
Тема | Bug? 8.0 does not use partial index |
Дата | |
Msg-id | EDB533C004CC24B481D38FEC@rambutan.pingpong.net обсуждение исходный текст |
Ответы |
Re: Bug? 8.0 does not use partial index
|
Список | pgsql-hackers |
Hi! Here's an odd thing. I use a partial index on a table: group_data CREATE TABLE group_data ( this_group_id integer NOT NULL, group_id integer -- ... ); create index foo on group_data(this_group_id) where group_id is null; there are approx 1 million tuples where this_group_id=46, but only 4 (four) where group_id is null. So I would expect this query to use the index: select * from group_data where this_group_id=46 and group_id is null. On 7.4.5, it uses the index, but on 8.0rc5, it does not: 7.4.5=# 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..40383.21 rows=108786 width=43) (actual time=0.154..0.176 rows=4 loops=1) Index Cond: (this_group_id = 46) Filter: (group_id IS NULL)Total runtime:0.241 ms (4 rows) 8.0.0rc5=# 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..140180.91 rows=211378 width=45) (actual time=383.689..32991.424 rows=4 loops=1) Filter: ((group_id IS NULL) AND (this_group_id = 46))Total runtime: 32991.469ms (3 rows) Time: 32992.812 ms This is bad. But it gets worse: 8.0.0rc5=# 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 group_data_tgid_gidnull_idx on group_data (cost=0.00..145622.85 rows=78985 width=45) (actual time=0.033..0.039 rows=4 loops=1) Index Cond: ((this_group_id = 46) AND (this_group_id = 46)) Filter: (group_id IS NULL)Total runtime: 0.086 ms (4 rows) Time: 1.912 ms Don't tell me this is not a bug? this_group_id = 46 and this_group_id = 46 seems like a pretty odd way to get a query to use an index? Need more specific info, please mail me! Regards, Palle
В списке pgsql-hackers по дате отправления: