From: Alan Hodgson <ahodgson@simkin.ca>
To: pgsql-performance@postgresql.org
Subject: Re: How to speed up min/max(id) in 50M rows table?
Date: Fri, 12 Oct 2007 15:04:40 -0700
Message-ID: <200710121504.40365@hal.medialogik.com> (raw)
In-Reply-To: <BAY124-W50228E83905C05AA18AF57F5A00@phx.gbl>
References: <BAY124-W503931258D0AE13AF37546F5A00@phx.gbl>
<470FE136.2010601@emolecules.com>
<BAY124-W50228E83905C05AA18AF57F5A00@phx.gbl>
On Friday 12 October 2007, henk de wit <henk53602@hotmail.com> wrote:
> > select payment_id from transactions order by payment_id desc limit 1;
>
> This one is indeed instant! Less than 50ms. In my case I can't use it for
> max though because of the fact that payment_id can be null (which is an
> unfortunate design choice). The other variant however didn't become
> instant. I.e. I tried:
>
> select time from transactions where payment_id = 67 order by time asc
> limit 1;
>
> But this one is still really slow.
If you had a compound index on payment_id,time (especially with a WHERE time
IS NOT NULL conditional) it would likely speed it up.
--
Ghawar is dying
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: ahodgson@simkin.ca
Subject: Re: How to speed up min/max(id) in 50M rows table?
In-Reply-To: <200710121504.40365@hal.medialogik.com>
* 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