On 5 November 2013 14:28, Leonardo Francalanci <m_lists@yahoo.it> wrote:
> Either my sql is not correct (likely), or my understanding of the minmax
> index is
> not correct (even more likely), or the minmax index is not usable in a
> random inputs
> scenario.
Please show the real world SQL you intend to run, so we can comment on
it. Inventing a use case that breaks effectiveness of any optimisation
is always easy, but the question is whether the use case is likely or
even desirable.
If we have a query to show the most recent calls by a particular caller
SELECT *
FROM cdr
WHERE callerid = X
ORDER BY call_timestamp DESC
LIMIT 100
then this could potentially be optimised using a minmax index, by
traversing the data ranges in call_timestamp order. That is not part
of the code in this initial release, since the main use case is for
WHERE call_timestamp >= X, or WHERE primarykey = Y
I don't believe there is a credible business case for running that
same query but without the ORDER BY and LIMIT, since it could
potentially return gazillions of rows, so it isn't surprising at all
that it would access a large % of the table. Saying "but I really do
want to run it" isn't an argument in favour of it being a sensible
query to run - we are only interested in optimising sensible real
world queries.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services