From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: View not using index
Date: Mon, 13 Jun 2005 10:18:24 -0400
Message-ID: <19466.1118672304@sss.pgh.pa.us> (raw)
In-Reply-To: <ea465cec82454372ead9c8a92a795d71@implements.be>
References: <9e1745a6fe1ae29c6f1c824128871d36@implements.be>
<200506131705.35852.mr-russ@pws.com.au>
<ea465cec82454372ead9c8a92a795d71@implements.be>
Yves Vindevogel <yves.vindevogel@implements.be> writes:
> rvponp=# explain select * from vw_document_pagesperjob limit 10 ;
> QUERY PLAN
> ------------------------------------------------------------------------
> ----------------------
> Limit (cost=82796.59..82796.72 rows=10 width=706)
> -> Subquery Scan vw_document_pagesperjob (cost=82796.59..90149.20
> rows=588209 width=706)
> -> Sort (cost=82796.59..84267.11 rows=588209 width=74)
> Sort Key: tblprintjobs.descpages,
> tblprintjobs.documentname
> -> Seq Scan on tblprintjobs (cost=0.00..26428.61
> rows=588209 width=74)
> (5 rows)
In general, putting an ORDER BY inside a view isn't a great idea ---
it's not legal per SQL spec (hence not portable), and it defeats most
forms of optimization of the view.
CVS tip is actually able to do what you wish with the above case, but no
existing release will optimize the view's ORDER BY in light of a LIMIT
that's outside the view.
regards, tom lane
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, yves.vindevogel@implements.be
Subject: Re: View not using index
In-Reply-To: <19466.1118672304@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