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 <[EMAIL PROTECTED]> wrote:
Palle Girgensohn <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
