Re: overzealous sorting?

Le Tue, 27 Sep 2011 12:45:00 +1000,
anthony(dot)shipman(at)symstream(dot)com a écrit :

> On Monday 26 September 2011 19:39, Marc Cousin wrote:
> > Because Index Scans are sorted, not Bitmap Index Scans, which
> > builds a list of pages to visit, to be then visited by the Bitmap
> > Heap Scan step.
> >
> > Marc.
>
> Where does this bitmap index scan come from? It seems to negate the
> advantages of b-tree indexes described in the section "Indexes and
> ORDER BY" of the manual. If I do "set enable_bitmapscan = off;" the
> query runs a bit faster although with a larger time range it reverts
> to a sequential scan.
>

Bitmap Index Scan is just another way to use a btree index. It is often
used when a bigger part of a table is required, as it costs more than
plain index scan to retrieve a few records, but less when a lot of
records are needed.

Your tests show that index scans are a bit faster on this query. But it
is probably true only when most needed data is cached, which is probably
your case, as you are doing tests using the same query all the time.
The bitmap index scan is probably cheaper when data isn't in cache. You
could also see the bitmap index scan as safer, as it won't perform as
bad when data is not cached (less random IO) :)

The thing is, the optimizer doesn't know if your data will be in cache
when you will run your query… if you are sure most of your data is in
the cache most of the time, you could try to tune random_page_cost
(lower it) to reflect that data is cached. But if the win is small on
this query, it may not be worth it.