From: | John Meinel <john(at)johnmeinel(dot)com> |
---|---|
To: | Jaime Casanova <systemguards(at)yahoo(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Sequential Scan with LIMIT |
Date: | 2004-10-28 15:27:03 |
Message-ID: | 41810FC7.6070501@johnmeinel.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Jaime Casanova wrote:
[...]
>
> In http://www.postgresql.org/docs/faqs/FAQ.html under
> "4.8) My queries are slow or don't make use of the
> indexes. Why?" says:
>
> "However, LIMIT combined with ORDER BY often will use
> an index because only a small portion of the table is
> returned. In fact, though MAX() and MIN() don't use
> indexes, it is possible to retrieve such values using
> an index with ORDER BY and LIMIT:
> SELECT col
> FROM tab
> ORDER BY col [ DESC ]
> LIMIT 1;"
>
> So, maybe you can try your query as
>
> SELECT col FROM mytable
> WHERE col = 'myval'
> ORDER BY col
> LIMIT 1;
>
> regards,
> Jaime Casanova
Thanks for the heads up. This actually worked. All queries against that
table have turned into index scans instead of sequential.
John
=:->
From | Date | Subject | |
---|---|---|---|
Next Message | Anjan Dave | 2004-10-28 15:38:26 | Re: Summary: can't handle large number of INSERT/UPDATEs |
Previous Message | Alban Medici (NetCentrex) | 2004-10-28 08:01:02 | Re: Performance Anomalies in 7.4.5 |