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

Re: Bug? 8.0 does not use partial index

From: Palle Girgensohn <girgen(at)pingpong(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Bug? 8.0 does not use partial index
Date: 2005-01-13 21:57:27
Message-ID: 33D183419CC483774A99F5F2@palle.girgensohn.se (view raw or flat)
Thread:
Lists: 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 Scan on 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 Scan on 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 Scan using 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 Scan using 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 Scan using 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 Scan using 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(at)sss(dot)pgh(dot)pa(dot)us> 
wrote:

> Palle Girgensohn <girgen(at)pingpong(dot)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





In response to

Responses

pgsql-hackers by date

Next:From: Palle GirgensohnDate: 2005-01-13 22:00:50
Subject: Re: Bug? 8.0 does not use partial index
Previous:From: Tom LaneDate: 2005-01-13 21:37:50
Subject: Re: Slow PL/pgSQL 8.0.RC5 (7.4.6. 3times faster)

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