Re: Bug? 8.0 does not use partial index

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