> Cases with lots of irrelevant indexes. Zoltan's example had 4 indexes
> per child table, only one of which was relevant to the query. In your
> test case there are no irrelevant indexes, which is why the runtime
> didn't change.
Mmh... I must be doing something wrong. It looks to me it's not just
the irrelevant indexes: it's the "order by" that counts. If I remove that
times are the same with and without the patch:
using the test case:
explain select * from inh_parent
where timestamp1 between '2010-04-06' and '2010-06-25'
this one runs in the same time with the patch; but adding:
order by timestamp2
made the non-patched version run 3 times slower.
My test case:
create table t (a integer, b integer, c integer, d integer, e text);
DO $$DECLARE i int;
BEGIN
FOR i IN 0..2000 LOOP
EXECUTE 'create table t' || i || ' ( CHECK (a >' || i*10 || '
and a <= ' || (i+1)*10 || ' ) ) INHERITS (t)';
EXECUTE 'create index taidx' || i || ' ON t' || i || ' (a)';
EXECUTE 'create index tbidx' || i || ' ON t' || i || ' (b)';
EXECUTE 'create index tcidx' || i || ' ON t' || i || ' (c)';
EXECUTE 'create index tdidx' || i || ' ON t' || i || ' (d)';
END LOOP;
END$$;
explain select * from t where a > 1060 and a < 109000
this runs in 1.5 secs with and without the patch. But if I add
order by b
the non-patched version runs in 10 seconds.
Am I getting it wrong?