From: | Palle Girgensohn <girgen(at)pingpong(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Bug? 8.0 does not use partial index |
Date: | 2005-01-13 18:44:44 |
Message-ID: | EDB533C004CC24B481D38FEC@rambutan.pingpong.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 Scan using 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 Scan on 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.469 ms
(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 Scan using 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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2005-01-13 18:52:40 | Re: [HACKERS] Win32 config file extension, capitalization |
Previous Message | D'Arcy J.M. Cain | 2005-01-13 18:22:42 | Re: Much Ado About COUNT(*) |