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

Re: "select max/count(id)" not using index

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Ryszard Lach <siaco(at)autograf(dot)pl>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: "select max/count(id)" not using index
Date: 2003-12-22 10:56:50
Message-ID: 3FE6CDF2.10608@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-performance
> I have a table with 24k records and btree index on column 'id'. Is this
> normal, that 'select max(id)' or 'select count(id)' causes a sequential
> scan? It takes over 24 seconds (on a pretty fast machine):
> 
> => explain ANALYZE select max(id) from ogloszenia;

Yes, it is.  It is a known issue with Postgres's extensible operator 
architecture.

The work around is to have an index on the id column and do this instead:

SELECT id FROM ogloszenia ORDER BY id DESC LIMIT 1;

Which will be really fast.

Chris


In response to

pgsql-performance by date

Next:From: Evil AzraelDate: 2003-12-22 10:59:58
Subject: Re: "select max/count(id)" not using index
Previous:From: Ryszard LachDate: 2003-12-22 10:39:18
Subject: "select max/count(id)" not using index

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