X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7043C52A56 for ; Fri, 4 Mar 2005 11:26:20 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25942-06 for ; Fri, 4 Mar 2005 11:26:11 +0000 (GMT) Received: from ylpvm43.prodigy.net (ylpvm43-ext.prodigy.net [207.115.57.74]) by svr1.postgresql.org (Postfix) with ESMTP id 7DC6752A5C for ; Fri, 4 Mar 2005 11:26:10 +0000 (GMT) Received: from one (adsl-68-124-176-26.dsl.pltn13.pacbell.net [68.124.176.26]) by ylpvm43.prodigy.net (8.12.10 outbound/8.12.10) with ESMTP id j24BQBoo030878 for ; Fri, 4 Mar 2005 06:26:12 -0500 Received: from [192.168.69.8] (rondesktop [192.168.69.8]) by one (Postfix) with ESMTP id B5264F1CF9 for ; Fri, 4 Mar 2005 03:26:10 -0800 (PST) Date: Fri, 4 Mar 2005 03:27:23 -0800 (PST) From: Ron Mayer X-X-Sender: mayer@greenie.cheapcomplexdevices.com To: pgsql-performance@postgresql.org Subject: Query's fast standalone - slow as a subquery. In-Reply-To: <20050304002724.650A95AF649@svr4.postgresql.org> Message-ID: References: <20050304002724.650A95AF649@svr4.postgresql.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/71 X-Sequence-Number: 10776 I have a query that runs quite quickly using a hash join when run standalone. When I use this query as a subquery the planner always seems to pick a differnt plan with an order of magnitude worse performance. This bad plan is chosen even when the outer sql statement is a trivial expression like this: select * from (query) as a; which I believe should be a no-op. Should the optimizer have noticed that it could have used a hash join in this case? Anything I can do to help convince it to? Explain analyze output follows. Thanks, Ron ============================================================================ fli=# explain analyze SELECT * from (select * from userfeatures.points join icons using (iconid) where the_geom && setSRID('BOX3D(-123.40 25.66,-97.87 43.17)'::BOX3D, -1 )) as upf ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..446.42 rows=1 width=120) (actual time=-0.096..7928.546 rows=15743 loops=1) Join Filter: ("outer".iconid = "inner".iconid) -> Seq Scan on points (cost=0.00..444.43 rows=1 width=82) (actual time=0.096..132.255 rows=15743 loops=1) Filter: (the_geom && '010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry) -> Seq Scan on icons (cost=0.00..1.44 rows=44 width=42) (actual time=0.006..0.242 rows=44 loops=15743) Total runtime: 8005.766 ms (6 rows) fli=# explain analyze select * from userfeatures.points join icons using (iconid) where the_geom && setSRID('BOX3D(-123.40 25.66,-97.87 43.17)'::BOX3D, -1 ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=1.55..682.84 rows=15789 width=120) (actual time=0.641..320.002 rows=15743 loops=1) Hash Cond: ("outer".iconid = "inner".iconid) -> Seq Scan on points (cost=0.00..444.43 rows=15794 width=82) (actual time=0.067..94.307 rows=15743 loops=1) Filter: (the_geom && '010300000001000000050000009A99999999D95EC0295C8FC2F5A839409A99999999D95EC0F6285C8FC295454048E17A14AE7758C0F6285C8FC295454048E17A14AE7758C0295C8FC2F5A839409A99999999D95EC0295C8FC2F5A83940'::geometry) -> Hash (cost=1.44..1.44 rows=44 width=42) (actual time=0.530..0.530 rows=0 loops=1) -> Seq Scan on icons (cost=0.00..1.44 rows=44 width=42) (actual time=0.026..0.287 rows=44 loops=1) Total runtime: 397.003 ms (7 rows)