pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query's fast standalone - slow as a subquery.
2+ messages / 2 participants
[nested] [flat]

* Query's fast standalone - slow as a subquery.
@ 2005-03-04 11:27 Ron Mayer <rm_pg@cheapcomplexdevices.com>
  2005-03-04 15:22 ` Re: Query's fast standalone - slow as a subquery. Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Ron Mayer @ 2005-03-04 11:27 UTC (permalink / raw)
  To: pgsql-performance


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)








^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Query's fast standalone - slow as a subquery.
  2005-03-04 11:27 Query's fast standalone - slow as a subquery. Ron Mayer <rm_pg@cheapcomplexdevices.com>
@ 2005-03-04 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-03-04 15:22 UTC (permalink / raw)
  To: Ron Mayer <rm_pg@cheapcomplexdevices.com>; +Cc: pgsql-performance

Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>    ->  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 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)

Apparently the selectivity of the && condition is misestimated in the
first case (note the radically wrong rowcount estimate), leading to an
inefficient join plan choice.  I suppose this is a bug in the postgis
selectivity routines --- better complain to them.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-04 15:22 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-04 11:27 Query's fast standalone - slow as a subquery. Ron Mayer <rm_pg@cheapcomplexdevices.com>
2005-03-04 15:22 ` Tom Lane <tgl@sss.pgh.pa.us>

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