Hm i have no idea how to rewrite 'OR' conditions with subqueries as joins (exept using 'UNION' as writen end of my message) Are you sure it is possible? 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)) > -----Original Message----- > From: Jim C. Nasby [mailto:jim@xxxxxxxxx] > > Try changing to a just a join and see if it works. > > On Mon, Sep 18, 2006 at 05:35:52PM +0400, Boguk Maxim wrote: > > 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,4631 > 35,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 > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > > > -- > Jim Nasby jim@xxxxxxxxx > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) >
