Re: Functional dependencies and GROUP BY - Mailing list pgsql-hackers

From Alex Hunsaker
Subject Re: Functional dependencies and GROUP BY
Date
Msg-id AANLkTikMZc2b0pLNC5ItldlhM8OVvzCEyMUgqQOpNtYH@mail.gmail.com
Whole thread Raw
In response to Re: Functional dependencies and GROUP BY  (Alex Hunsaker <badalex@gmail.com>)
List pgsql-hackers
On Fri, Jul 16, 2010 at 22:29, Alex Hunsaker <badalex@gmail.com> wrote:
> (FYI I do plan on doing some performance testing with large columns
> later, any other requests?)

And here are the results.  All tests are with an empty table with 1500
int4 columns.  There is a unique non null index on the first column.

(non assert build)
A: select count(1) from (select * from test group by ...1500 columns...) as res;
B: select count(1) from (select * from test group by a_0) as res; --
a_0 has the not null unique index

CVS A:  360ms
PATCH A: 370ms
PATCH B: 60ms

1500 indexes (one per column, on the column):
CVS: A: 670ms
PATCH A: 850ms
PATCH B: 561ms

So it seems for tables with lots of columns the patch is faster, at
least when you omit all the columns from the group by.  I suspect for
most "normal" (5-20 columns) usage it should be a wash.

(Stupid question) Does anyone know why HEAD is quite a bit slower when
there are lots off indexes?  Do we end up looping and perhaps locking
them or something?


pgsql-hackers by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: SHOW TABLES
Next
From: Alex Hunsaker
Date:
Subject: Re: Functional dependencies and GROUP BY