From: Tom Lane <tgl@sss.pgh.pa.us>
To: Francois Deliege <fdeliege@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Distinct + Limit
Date: Wed, 28 Mar 2012 10:13:01 -0400
Message-ID: <22625.1332943981@sss.pgh.pa.us> (raw)
In-Reply-To: <16737833.463.1332881676120.JavaMail.geo-discussion-forums@pbcpw7>
References: <5833589.541.1332880671745.JavaMail.geo-discussion-forums@pbbnv8>
<16737833.463.1332881676120.JavaMail.geo-discussion-forums@pbcpw7>
Francois Deliege <fdeliege@gmail.com> writes:
> I have the following table with millions of rows:
> CREATE TABLE table1
> (
> col1 text,
> col2 text,
> col3 text,
> col4 text,
> col5 text,
> col6 text
> )
> select col1 from table1 group by col1 limit 1;
> select distinct on (col1) col1 from table1 limit 1;
> select col1 from table1 group by col1 limit 2;
> select distinct on (col1) col1 from table1 limit 2;
> Performing any of these following queries results in a full sequential
scan, followed by a hash aggregate, and then the limit.
Well, if you had an index on the column, you would get a significantly
better plan ...
> Similarly, the following query results in a sequential scan:
> select * from table1 where col1 <> col1;
> This query is generated by the Sequel library abstraction layer in Ruby when filtering record based on a empty array of values. We fixed this by handling that case on the client side, but originally thought the server would have rewritten it and sent a empty result set.
It does not, and never will, because that would be an incorrect
optimization. "col1 <> col1" isn't constant false, it's more like
"col1 is not null". I'd suggest "WHERE FALSE", or "WHERE 1 <> 1"
if you must, to generate a provably false constraint.
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, fdeliege@gmail.com
Subject: Re: Distinct + Limit
In-Reply-To: <22625.1332943981@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