X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 567BD474E53 for ; Mon, 17 Feb 2003 12:18:29 -0500 (EST) Received: from boromir.vpop.net (dns1.vpop.net [207.178.248.2]) by postgresql.org (Postfix) with ESMTP id AAAEC474E42 for ; Mon, 17 Feb 2003 12:18:28 -0500 (EST) Received: from c5cust187.starstream.net (c5cust187.starstream.net [206.170.161.187]) by boromir.vpop.net (Postfix) with ESMTP id 43B513A5EBF; Mon, 17 Feb 2003 09:18:32 -0800 (PST) Subject: Re: sub-query optimization From: Brad Hilton To: Tomasz Myrta Cc: jasiek@serwer.skawsoft.com.pl, pgsql-sql@postgresql.org In-Reply-To: <20030215095213.M15935@klaster.net> References: <1045246939.29966.22.camel@aragorn.vpop.net> <20030214192113.GA26987@serwer> <1045251747.29974.45.camel@aragorn.vpop.net> <20030214205922.M13630@klaster.net> <1045262371.29955.124.camel@aragorn.vpop.net> <20030215095213.M15935@klaster.net> Content-Type: text/plain Organization: Message-Id: <1045502407.7192.10.camel@aragorn.vpop.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 17 Feb 2003 09:20:07 -0800 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/372 X-Sequence-Number: 11770 On Fri, 2003-02-14 at 17:52, Tomasz Myrta wrote: > It looks ok now. Probably it needs some cosmetics changes. > > > > select a.* > > from > > categories c cross join category_map m > > join article_categories ac on (c.id = ac.category_id and m.child_id = > > ac.category_id) > > join articles a on (a.id = ac.article_id) > > where > > m.parent_id=1 and > > not c.restrict_views and > > m.child_id = c.id and > > a.post_status='publish' > > > Can you send explain analyze this query? Maybe table > joins should be reordered or they need other indexes they have? > Sure, I appreciate your interest and help. I modified the above query to do a "select distinct a.*" since I need to fetch all fields and need them grouped by id. I'm also including a query which uses derived tables and avoids the penalty of "distinct a.*." The second query is much faster because of this, but it's still slower than I was hoping. ~3 seconds for a query isn't going to fly. :( If you can see any warning signs from the "explain" output, I'd love to hear from you. Thanks, -Brad ---------------------------------- explain analyze select distinct a.* from categories c cross join category_map m join article_categories ac on (c.id = ac.category_id and m.child_id = ac.category_id) join articles a on (a.id = ac.article_id) where m.parent_id=1 and not c.restrict_views and m.child_id = c.id and a.post_status='publish' --------------------------------- Unique (cost=61058.89..68058.89 rows=20000 width=203) (actual time=7649.35..8465.96 rows=100000 loops=1) -> Sort (cost=61058.89..61558.89 rows=200000 width=203) (actual time=7649.35..7898.56 rows=200000 loops=1) Sort Key: a.id, a.user_id, a.blog_id, a.remote_ip, a.create_time, a.publish_time, a.update_time, a.title, a.body, a.long_body, a.excerpt, a.post_status, a.publish_date -> Hash Join (cost=5133.25..17614.25 rows=200000 width=203) (actual time=590.36..6029.65 rows=200000 loops=1) Hash Cond: ("outer".article_id = "inner".id) -> Hash Join (cost=18.33..6499.33 rows=200000 width=16) (actual time=2.08..801.69 rows=200000 loops=1) Hash Cond: ("outer".category_id = "inner".id) -> Seq Scan on article_categories ac (cost=0.00..2981.00 rows=200000 width=8) (actual time=0.01..293.28 rows=200000 loops=1) -> Hash (cost=18.01..18.01 rows=131 width=8) (actual time=1.72..1.72 rows=0 loops=1) -> Hash Join (cost=6.64..18.01 rows=131 width=8) (actual time=0.84..1.58 rows=131 loops=1) Hash Cond: ("outer".child_id = "inner".id) -> Seq Scan on category_map m (cost=0.00..9.07 rows=131 width=4) (actual time=0.02..0.43 rows=131 loops=1) Filter: (parent_id = 1) -> Hash (cost=6.31..6.31 rows=131 width=4) (actual time=0.40..0.40 rows=0 loops=1) -> Seq Scan on categories c (cost=0.00..6.31 rows=131 width=4) (actual time=0.02..0.24 rows=131 loops=1) Filter: (NOT restrict_views) -> Hash (cost=2885.00..2885.00 rows=100000 width=187) (actual time=588.13..588.13 rows=0 loops=1) -> Seq Scan on articles a (cost=0.00..2885.00 rows=100000 width=187) (actual time=0.03..429.67 rows=100000 loops=1) Filter: (post_status = 'publish'::character varying) Total runtime: 18544.75 msec -------------------------------- explain analyze select articles.* from (select article_id from article_categories ac, categories c, category_map cm where ac.category_id = c.id and ac.category_id = cm.child_id and c.id = cm.child_id and c.restrict_views = FALSE and cm.parent_id = 1 group by article_id) X join articles on (articles.id = X.article_id) where articles.post_status = 'publish' -------------------------------- Merge Join (cost=26538.07..30754.75 rows=20000 width=191) (actual time=1736.36..3079.64 rows=100000 loops=1) Merge Cond: ("outer".id = "inner".article_id) -> Index Scan using articles_pkey on articles (cost=0.00..3616.68 rows=100000 width=187) (actual time=0.06..674.13 rows=100000 loops=1) Filter: (post_status = 'publish'::character varying) -> Sort (cost=26538.07..26588.07 rows=20000 width=16) (actual time=1736.27..1800.42 rows=100000 loops=1) Sort Key: x.article_id -> Subquery Scan x (cost=24109.30..25109.30 rows=20000 width=16) (actual time=1073.54..1594.71 rows=100000 loops=1) -> Group (cost=24109.30..25109.30 rows=20000 width=16) (actual time=1073.54..1447.94 rows=100000 loops=1) -> Sort (cost=24109.30..24609.30 rows=200000 width=16) (actual time=1073.52..1191.72 rows=200000 loops=1) Sort Key: ac.article_id -> Hash Join (cost=18.66..6499.66 rows=200000 width=16) (actual time=2.14..777.38 rows=200000 loops=1) Hash Cond: ("outer".category_id = "inner".id) -> Seq Scan on article_categories ac (cost=0.00..2981.00 rows=200000 width=8) (actual time=0.01..304.06 rows=200000 loops=1) -> Hash (cost=18.33..18.33 rows=131 width=8) (actual time=1.80..1.80 rows=0 loops=1) -> Hash Join (cost=6.97..18.33 rows=131 width=8) (actual time=0.78..1.65 rows=131 loops=1) Hash Cond: ("outer".child_id = "inner".id) -> Seq Scan on category_map cm (cost=0.00..9.07 rows=131 width=4) (actual time=0.01..0.54 rows=131 loops=1) Filter: (parent_id = 1) -> Hash (cost=6.64..6.64 rows=131 width=4) (actual time=0.39..0.39 rows=0 loops=1) -> Seq Scan on categories c (cost=0.00..6.64 rows=131 width=4) (actual time=0.04..0.25 rows=131 loops=1) Filter: (restrict_views = false) Total runtime: 3221.05 msec