From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: pgsql-performance@postgresql.org
Subject: Query's fast standalone - slow as a subquery.
Date: Fri, 4 Mar 2005 03:27:23 -0800 (PST)
Message-ID: <Pine.LNX.4.58.0503040319400.2227@greenie.cheapcomplexdevices.com> (raw)
In-Reply-To: <20050304002724.650A95AF649@svr4.postgresql.org>
References: <20050304002724.650A95AF649@svr4.postgresql.org>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rm_pg@cheapcomplexdevices.com
Subject: Re: Query's fast standalone - slow as a subquery.
In-Reply-To: <Pine.LNX.4.58.0503040319400.2227@greenie.cheapcomplexdevices.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox