Re: [PATCH] Enable min/max optimization for bool_and/bool_or/every - Mailing list pgsql-hackers

From Marti Raudsepp
Subject Re: [PATCH] Enable min/max optimization for bool_and/bool_or/every
Date
Msg-id CABRT9RD4J8HW9vZd7NQjcUbt+Ni1g-B-cQtWNFRw_FvOuB=egg@mail.gmail.com
Whole thread Raw
In response to Re: [PATCH] Enable min/max optimization for bool_and/bool_or/every  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Wed, Feb 8, 2012 at 19:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I applied this patch, since I was busy applying catalog changes from you
> anyway ;-).

Thanks :)

> I did think of a possible reason to reject the patch: with this change,
> the planner will take longer to plan queries involving bool_and() et al,
> since planagg.c will spend time looking (usually fruitlessly) for an
> index-based plan.

Good point, I should have done those measurements up front. Anyway,
since I've often noticed \timing to be unreliable for short queries, I
decided to retry your test with pgbench.

Long story short, I measured 27% overhead in the un-indexed column
case and 33% overhead for an indexed column. That's a lot more than I
expected. I even rebuilt and retried a few times to make sure I hadn't
botched something. The benchmark script is attached.

UNPATCHED
select bool_and(b) from unindexed;
tps = 13787.023113 (excluding connections establishing)
tps = 13880.484788 (excluding connections establishing)
tps = 13784.654542 (excluding connections establishing)
select bool_and(b) from indexed;
tps = 12536.650703 (excluding connections establishing)
tps = 12647.767993 (excluding connections establishing)
tps = 12500.956407 (excluding connections establishing)

PATCHED
select bool_and(b) from unindexed;
tps = 10096.834678 (excluding connections establishing)
tps = 10110.182425 (excluding connections establishing)
tps = 10103.904500 (excluding connections establishing)
select bool_and(b) from indexed;
tps = 8373.631407 (excluding connections establishing)
tps = 8659.917173 (excluding connections establishing)
tps = 8473.899896 (excluding connections establishing)

Regards,
Marti

Attachment

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Bugs/slowness inserting and indexing cubes
Next
From: Christian Nicolaisen
Date:
Subject: Re: patch : Allow toast tables to be moved to a different tablespace