From: Francois Deliege <fdeliege@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Distinct + Limit
Date: Tue, 27 Mar 2012 13:54:36 -0700 (PDT)
Message-ID: <16737833.463.1332881676120.JavaMail.geo-discussion-forums@pbcpw7> (raw)
In-Reply-To: <5833589.541.1332880671745.JavaMail.geo-discussion-forums@pbbnv8>
References: <5833589.541.1332880671745.JavaMail.geo-discussion-forums@pbbnv8>
Hi list,
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. An optimization could be to stop the sequential scan as soon as the limit of results has been reached. Am I missing something?
Limit (cost=2229280.06..2229280.08 rows=2 width=8)
-> HashAggregate (cost=2229280.06..2229280.21 rows=15 width=8)
-> Seq Scan on table1 (cost=0.00..2190241.25 rows=15615525 width=8)
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.
I would greatly appreciate any help on speeding up these without having to rewrite the queries on the client side.
Thanks,
Francois
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: fdeliege@gmail.com
Subject: Re: Distinct + Limit
In-Reply-To: <16737833.463.1332881676120.JavaMail.geo-discussion-forums@pbcpw7>
* 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