On Fri, Jun 09, 2006 at 12:40:26PM +0300, Andrus wrote:
> > 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.
>
> Thank you. It makes query fast.
>
> > Or maybe you could
Tom,
> Because it doesn't help --- the system still has to do the sort.
It can help a lot in this case.
kuupaev is sales date
kellaaeg is sales time
Postgres can use kuupaev index to fetch first 100 rows plus a number of more
rows whose kellaaeg value is equal to kellaaeg in 100 th row. I have
> 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.
Thank you. It makes query fast.
> Or maybe you could try increasing the server's
> work mem. The sort will be much slower
> Btw. have you run ANALYZE on the table recently?
I have autovacuum with default statitics settings running so I expect that
it is analyzed.
> What is the number
> of distinct values in the 'kuupaev' column?
select count(distinct kuupaev) from makse
returns 61
kuupaev is sales date.
So this
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> And even then you better have a pretty high correlation on the first
> column, otherwise you'll still get a seqscan.
Not with the LIMIT. (If he were fetching the whole table, very possibly
the sort would be the right plan anyway.)
On Thu, Jun 08, 2006 at 03:20:55PM -0400, Tom Lane wrote:
> "Andrus" <[EMAIL PROTECTED]> writes:
> > Why Postgres 8.1 does not use makse_kuupaev_idx index in the following
> > query
> > ?
>
> Because it doesn't help --- the system still has to do the sort.
> You'd need a two-column index on both
e
thing in ram.
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] 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
"Andrus" <[EMAIL PROTECTED]> writes:
> Why Postgres 8.1 does not use makse_kuupaev_idx index in the following query
> ?
Because it doesn't help --- the system still has to do the sort.
You'd need a two-column index on both of the ORDER BY columns to avoid
sorting.
regards
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 i
If you want to benefit from the usage of an index, the query has to
contain some WHERE conditions (on the indexed columns). This is a
'select all' query - there is no way to speed it up using index.
Tomas
> > Why Postgres 8.1 does not use makse_kuupaev_idx index in the
following query
> > ?
> >
>
10 matches
Mail list logo
