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 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