Message-ID: <44887875.8000502@fuzzy.cz>
Date: Thu, 08 Jun 2006 21:20:21 +0200
From: Tomas Vondra <tv@fuzzy.cz>
User-Agent: Mail/News 1.5 (X11/20060424)
MIME-Version: 1.0
To: Andrus <eetasoft@online.ee>
CC: pgsql-performance@postgresql.org
Subject: Re: Why date index is not used
References: <e69rq9$2a1b$1@news.hub.org>
In-Reply-To: <e69rq9$2a1b$1@news.hub.org>
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: 7bit

More precisely - the Postgres could use the index to speed up the
sorting, but in this case the sorting is very fast (less than one
second according to the output), so Postgres probably decided not
to use the index because it would be slower.

Btw. have you run ANALYZE on the table recently? What is the number
of distinct values in the 'kuupaev' column?

Tomas

> Why Postgres 8.1 does not use makse_kuupaev_idx index in the following query 
> ?
> 
> How to speed this query up ?
> 
> explain analyze select * from makse order by kuupaev desc, kellaaeg desc 
> limit 100
> 
> "Limit  (cost=62907.94..62908.19 rows=100 width=876) (actual 
> time=33699.551..33701.001 rows=100 loops=1)"
> "  ->  Sort  (cost=62907.94..63040.49 rows=53022 width=876) (actual 
> time=33699.534..33700.129 rows=100 loops=1)"
> "        Sort Key: kuupaev, kellaaeg"
> "        ->  Seq Scan on makse  (cost=0.00..2717.22 rows=53022 width=876) 
> (actual time=0.020..308.502 rows=53028 loops=1)"
> "Total runtime: 37857.177 ms"
> 
> 
> CREATE TABLE makse(
>   kuupaev date,
>   kellaaeg char(6) NOT NULL DEFAULT ''::bpchar,
>   guid char(36) NOT NULL,
>   CONSTRAINT makse_pkey PRIMARY KEY (guid) )
> 
> 
> CREATE INDEX makse_kuupaev_idx  ON makse  USING btree  (kuupaev);
> 
> 
> Andrus.