Skip site navigation (1) Skip section navigation (2)

Re: plan time of MASSIVE partitioning ...

From: Leonardo Francalanci <m_lists(at)yahoo(dot)it>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Boszormenyi Zoltan <zb(at)cybertec(dot)at>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: plan time of MASSIVE partitioning ...
Date: 2010-10-29 20:08:57
Message-ID: 950077.88955.qm@web29019.mail.ird.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> 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?


      

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2010-10-29 20:11:18
Subject: Re: [PATCH] Cleanup: Compare pointers to NULL instead of 0
Previous:From: Tom LaneDate: 2010-10-29 19:59:08
Subject: Re: [PATCH] Cleanup: Compare pointers to NULL instead of 0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group