From: Dave Dutcher <dave@tridecap.com>
To: 'Tomas Vondra' <tv@fuzzy.cz>
To: 'Andrus' <eetasoft@online.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why date index is not used
Date: Thu, 8 Jun 2006 15:07:42 -0500
Message-ID: <01de01c68b37$33e22a80$8300a8c0@tridecap.com> (raw)
In-Reply-To: <44887875.8000502@fuzzy.cz>
Actually It looks to me like the sorting is the slow part of this query.
Maybe if you did create an index on both kuupaev and kellaaeg it might
make the sorting faster. Or maybe you could try increasing the server's
work mem. The sort will be much slower if the server can't do the whole
thing in ram.
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Tomas Vondra
> Sent: Thursday, June 08, 2006 2:20 PM
> To: Andrus
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Why date index is not used
>
>
> 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.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
http://archives.postgresql.org
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: dave@tridecap.com, tv@fuzzy.cz, eetasoft@online.ee
Subject: Re: Why date index is not used
In-Reply-To: <01de01c68b37$33e22a80$8300a8c0@tridecap.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