From: Francois Deliege <fdeliege@gmail.com>
Subject: Re: Distinct + Limit
Date: Tue, 27 Mar 2012 13:54:36 -0700 (PDT)
Organization: http://groups.google.com
Lines: 45
Message-ID: <16737833.463.1332881676120.JavaMail.geo-discussion-forums@pbcpw7>
References: <5833589.541.1332880671745.JavaMail.geo-discussion-forums@pbbnv8>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <5833589.541.1332880671745.JavaMail.geo-discussion-forums@pbbnv8>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com;
 posting-host=69.38.213.178;
	posting-account=MBeVbAoAAAArGxjJOpuhEJ6gPEjWX8_H
User-Agent: G2/1.0
To: pgsql-performance@postgresql.org

Hi list,

I have the following table with millions of rows:
=20
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.  An optimization could =
be to stop the sequential scan as soon as the limit of results has been rea=
ched.  Am I missing something?

Limit  (cost=3D2229280.06..2229280.08 rows=3D2 width=3D8)
  ->  HashAggregate  (cost=3D2229280.06..2229280.21 rows=3D15 width=3D8)
        ->  Seq Scan on table1  (cost=3D0.00..2190241.25 rows=3D15615525 wi=
dth=3D8)

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 whe=
n filtering record based on a empty array of values. We fixed this by handl=
ing that case on the client side, but originally thought the server would h=
ave rewritten it and sent a empty result set.

I would greatly appreciate any help on speeding up these without having to =
rewrite the queries on the client side.

Thanks,

Francois