pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query's fast standalone - slow as a subquery.
Date: Fri, 04 Mar 2005 10:22:56 -0500
Message-ID: <27481.1109949776@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.58.0503040319400.2227@greenie.cheapcomplexdevices.com>
References: <20050304002724.650A95AF649@svr4.postgresql.org>
	<Pine.LNX.4.58.0503040319400.2227@greenie.cheapcomplexdevices.com>

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



view thread (2+ messages)

Message-ID: <27481.1109949776@sss.pgh.pa.us>
Permalink:  ../27481.1109949776@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/27481.1109949776@sss.pgh.pa.us

 · 

reply

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: tgl@sss.pgh.pa.us, rm_pg@cheapcomplexdevices.com
  Subject: Re: Query's fast standalone - slow as a subquery.
  In-Reply-To: <27481.1109949776@sss.pgh.pa.us>

* 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