postgres version 8.1
all tables fresh vacuumed/analyzed
Problem table:
somedb=# \d el_comment
Table "public.el_comment"
Column | Type | Modifiers
------------------+-----------------------------+-----------------------------------------------------------------------
id | integer | not null default nextval(('public.documents_id_seq'::text)::regclass)
user_id | integer | not null
text_id | integer | not null
status | smallint | not null default 0
parent_id | integer |
Indexes:
"el_comment_pkey" PRIMARY KEY, btree (id)
"el_comment_parent_id" btree (parent_id)
"el_comment_text" btree (text_id)
"el_comment_user" btree (user_id)
Foreign-key constraints:
"delete_el_text" FOREIGN KEY (text_id) REFERENCES el_text(id) ON DELETE CASCADE
Problem query:
somedb=# EXPLAIN ANALYZE SELECT count(*) FROM el_comment WHERE ((parent_id IN (SELECT tt.id FROM el_comment as tt WHERE tt.user_id = 112 AND tt.status=1)) OR (text_id IN (SELECT el_text.id FROM el_text WHERE el_text.user_id = 112))) AND status=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=80641.51..80641.52 rows=1 width=0) (actual time=13528.870..13528.873 rows=1 loops=1)
-> Seq Scan on el_comment (cost=56.07..80352.97 rows=1154156 width=0) (actual time=113.866..13528.705 rows=15 loops=1)
Filter: ((status = 1) AND ((hashed subplan) OR (hashed subplan)))
SubPlan
-> Index Scan using el_text_user on el_text (cost=0.00..15.92 rows=12 width=4) (actual time=0.992..82.397 rows=12 loops=1)
Index Cond: (user_id = 112)
-> Index Scan using el_comment_user on el_comment tt (cost=0.00..40.14 rows=28 width=4) (actual time=8.748..21.661 rows=14 loops=1)
Index Cond: (user_id = 112)
Filter: (status = 1)
Total runtime: 13529.189 ms
(10 rows)
Now lets look output of both subqueries:
SELECT tt.id FROM el_comment as tt WHERE tt.user_id = 112 AND tt.status=1:
2766039
2766057
2244101
1929350
1929419
1929439
1490610
1052
2766033
2421000
2420878
611328
1019
1646
(14 rows)
and SELECT el_text.id FROM el_text WHERE el_text.user_id = 112
3758109
53688
1947631
1915372
1224421
1011606
13772
1017
463135
470614
575691
916229
(12 rows)
And put these values into query:
planet=# EXPLAIN ANALYZE SELECT count(*) FROM el_comment WHERE ((parent_id IN (2766039,2766057,2244101,1929350,1929419,1929439,1490610,1052,2766033,2421000,2420878,611328,1019,1646)) OR (text_id IN (3758109,53688,1947631,1915372,1224421,1011606,13772,1017,463135,470614,575691,916229))) AND status=1;
QUERY PLAN
Aggregate (cost=340.76..340.77 rows=1 width=0) (actual time=9.452..9.453 rows=1 loops=1)
-> Bitmap Heap Scan on el_comment (cost=52.24..340.71 rows=194 width=0) (actual time=5.431..9.269 rows=15 loops=1)
Recheck Cond: ((parent_id = 2766039) OR (parent_id = 2766057) OR (parent_id = 2244101) OR (parent_id = 1929350) OR (parent_id = 1929419) OR (parent_id = 1929439) OR (parent_id = 1490610) OR (parent_id = 1052) OR (parent_id = 2766033) OR (parent_id = 2421000) OR (parent_id = 2420878) OR (parent_id = 611328) OR (parent_id = 1019) OR (parent_id = 1646) OR (text_id = 3758109) OR (text_id = 53688) OR (text_id = 1947631) OR (text_id = 1915372) OR (text_id = 1224421) OR (text_id = 1011606) OR (text_id = 13772) OR (text_id = 1017) OR (text_id = 463135) OR (text_id = 470614) OR (text_id = 575691) OR (text_id = 916229))
Filter: (status = 1)
-> BitmapOr (cost=52.24..52.24 rows=194 width=0) (actual time=4.972..4.972 rows=0 loops=1)
-> Bitmap Index Scan on el_comment_parent_id (cost=0.00..2.00 rows=2 width=0) (actual time=0.582..0.582 rows=
1 loops=1)
Index Cond: (parent_id = 2766039)
....
14 same rows
....
-> Bitmap Index Scan on el_comment_text (cost=0.00..2.02 rows=13 width=0) (actual time=0.983..0.983 rows=0 loops=1)
Index Cond: (text_id = 3758109)
....
11 same rows
....
Total runtime: 10.368 ms
(58 rows)
Complete different result (1000x times faster). Issue look like planner can't/dont want try count both subquery's results and use bitmap scan.
And planner see amount of results from both subqueris small so bitmap scan must be look way better.
That is intended or bug?
PS: i got reasonable fast results via rewrite query as
select count(*) from
(
select t1.id from el_comment as t1 join el_comment as t2 on t1.parent_id=t2.id and t2.user_id=112 and t2.status=1 where t1.status=1
union
select t1.id from el_comment as t1 join el_text as t2 on t1.text_id=t2.id and t2.user_id=112 and t2.status=1
) as qqq;
but that is just workaround and work 2-5x time slower.
SY Maxim Boguk
