| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: The usual sequential scan, but with LIMIT ! |
| Date: | 2004-09-07 13:47:47 |
| Message-ID: | 29692.1094564867@sss.pgh.pa.us |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general pgsql-performance |
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <lists(at)boutiquenumerique(dot)com> writes:
> suppose I SELECT WHERE topic_id=2 ORDER BY topic_id ASC,id DESC.
> Postgres does a seq scan, but it could think a bit more and start at
> "first index node which has topic_id>2" (simple to find in a btree) then
> go backwards in the index.
If you write it as
SELECT WHERE topic_id=2 ORDER BY topic_id DESC,id DESC.
then an index on (topic_id, id) will work fine. The mixed ASC/DESC
ordering is not compatible with the index.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Garamond | 2004-09-07 13:47:56 | Dumping pg_shadow and pg_database as SQL using pg_dump |
| Previous Message | David Garamond | 2004-09-07 13:37:01 | Salt in encrypted password in pg_shadow |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2004-09-07 14:16:28 | Re: [PERFORM] TOAST tables, cannot truncate |
| Previous Message | G u i d o B a r o s i o | 2004-09-07 11:12:58 | Re: TOAST tables, cannot truncate |