Hannu Krosing <hannu@trust.ee> writes:
> can the optimizer make use of LIMIT, or some other hint that reaction
> time is preferred over speed of full query ?
That is on the to-do list. I think the hard part is working out the
details of when a top-level LIMIT applies to the costs of a lower-level
scan (for example, it does not if there's going to be a Sort in
between), and then figuring out how to transmit that information around.
The optimizer does most of its cost estimation bottom-up, so it might
be hard to do much in any but the simplest cases.
> The one way to find out would be actual benchmarking - if current
> optimizer prefers index scans it is possible to do a query using
> index scan, dro the index, somehow flush disk cache and then do the
> same query using seqscan+sort.
> If the latter is preferred anyway we would have no way to test ...
You can benchmark with and without index usage by starting your client
with PGOPTIONS="-fs" or PGOPTIONS="-fi" respectively --- that basically
puts a very heavy thumb on the scales when the optimizer is choosing
which to use ;-). However, I am not sure that I'd trust a small number
of benchmark results as a general guide to costs. I think the critical
factor for indexscan costs is how scattered the tuples are with respect
to the index order, and without some idea about that factor you'd have
no way to know if a particular benchmark result is high, low, or average.
regards, tom lane
