Skip site navigation (1) Skip section navigation (2)

Re: Order-by and indexes

From: Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
To: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Order-by and indexes
Date: 2011-06-30 10:01:13
Message-ID: BANLkTikyHAErwdxaTkJbzO179sTpKxg0TQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
>> I need to get the latest entry of a large table matching a certain
criteria.
>> This is my query:

>> SELECT * FROM "data" WHERE "data"."fk" = 238496 ORDER BY "data"."id" DESC
>> LIMIT 1

>A two-column index on (fk, id) would help for this.  If you think about
>the ordering of the index entries you'll see why.

I added an index for this, and now it goes much faster, thanks. The ones
that contain data is now superfast, 1-2 ms, but the ones that does not
match, and returns empty sets, are considerably slower, 150 -200 ms. Is this
something I have to live with, or are there any tricks that will make these
queries fast to?

Thanks again!

Odd-R.

In response to

pgsql-novice by date

Next:From: Michael WoodDate: 2011-06-30 11:05:29
Subject: Re: Connecting to PostgreSQL server
Previous:From: Vincent FicetDate: 2011-06-30 08:30:45
Subject: Re: pg_advisory_locks in a multithreaded application context

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group