pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org, Mike Rylander <mrylander@gmail.com>
Subject: Re: LIMIT causes SEQSCAN in subselect
Date: Sat, 11 Dec 2004 13:06:23 -0500
Message-ID: <17528.1102788383@sss.pgh.pa.us> (raw)
In-Reply-To: <200412102140.18456.josh@agliodbs.com>
References: <b918cf3d04121010405e6c963c@mail.gmail.com>
	<200412102140.18456.josh@agliodbs.com>

Josh Berkus <josh@agliodbs.com> writes:
> The fact that the estimator knows that the LIMIT is pointless because there 
> are less rows in the subselect than the LIMIT will return is not something we
> want to count on; sometimes the estimator has innaccurate information.

However, when the estimator is basing that estimate on the existence of
a unique index for the column, the estimate could be trusted.  There are
a couple of reasons that we don't perform that optimization at present,
though:

1. If the finished query plan doesn't actually *use* the index in
question, then dropping the index would not directly invalidate the
query plan, but nonetheless the query would be broken.  You could
subsequently get silently-wrong answers.

2. For the particular point at hand, there's an implementation problem,
which is that decisions about whether to flatten subqueries are taken
before we do any rowcount estimation.  So even if we discarded the LIMIT
clause once we realized it was redundant, it'd be too late to get the
optimal overall plan.

Point #1 is something I would like to fix whenever we get around to
implementing proper invalidation of cached plans.  There would need to
be a way to list "indirect" as well as direct dependencies of a plan.

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <17528.1102788383@sss.pgh.pa.us>
Permalink:  ../17528.1102788383@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17528.1102788383@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, josh@agliodbs.com, mrylander@gmail.com
  Subject: Re: LIMIT causes SEQSCAN in subselect
  In-Reply-To: <17528.1102788383@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