Skip site navigation (1) Skip section navigation (2)

Bug? 8.0 does not use partial index

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 (view raw or flat)
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


Responses

pgsql-hackers by date

Next:From: Marc G. FournierDate: 2005-01-13 18:52:40
Subject: Re: [HACKERS] Win32 config file extension, capitalization
Previous:From: D'Arcy J.M. CainDate: 2005-01-13 18:22:42
Subject: Re: Much Ado About COUNT(*)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group