Re: index speed and failed expectations?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>
Cc: "'rihad'" <rihad(at)mail(dot)ru>, pgsql-general(at)postgresql(dot)org
Subject: Re: index speed and failed expectations?
Date: 2008-08-04 14:20:13
Message-ID: 4322.1217859613@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> writes:
>> This query from the console:
>> select * from stats order by start_time;
>> takes 8 seconds before starting its output. Am I wrong in assuming that
>> the index on start_time should make ORDER BY orders of magnitude
>> faster?

> Postgresql won't use the index for queries like this.

"won't" -> "might not". It all depends on the relative cost estimates
for indexscan vs seqscan + sort. For a large table it's quite likely
that the latter will be cheaper, because it has a better-localized
access pattern.

> (What postgresql lacks is a first_row/all_rows hint like oracle)

That's spelled "LIMIT" ;-). Also, you can bias the choice in favor
of a fast-start plan if you use a cursor rather than a plain SELECT.
In that case the planner makes some allowance for the idea that
you might not want all the rows, or might be more interested in
getting the first ones quickly than minimizing the total time to
fetch all the rows.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Glyn Astill 2008-08-04 14:33:40 Re: index speed and failed expectations?
Previous Message Michael Fuhr 2008-08-04 14:18:41 Re: index speed and failed expectations?