pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow performance with Group By
2+ messages / 2 participants
[nested] [flat]
* Slow performance with Group By
@ 2004-11-09 00:09 Erik Norvelle <signups@norvelle.org>
2004-11-09 00:56 ` Re: Slow performance with Group By Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Erik Norvelle @ 2004-11-09 00:09 UTC (permalink / raw)
To: pgsql-performance
Greetings all,
This question has probably been asked many times, but I was unable to
use the list archives to search, since the term "Group" matches
thousands of of messages with the names of user groups in them... so
sorry if I'm repeating!
Here's the problem: I have a table of 10,000,000 records called
"indethom", each record representing a word in the works of a
particular author. Each record contains, among other columns, an
CHAR(5) column representing the "lemma" code (i.e. which word it is)
called "codelemm", and an integer representing a textual unit, i.e.
chapter or other division of a work (these are numbered consecutively
from 0 to around 50,000), called "sectref". What I want to do is find
out how many times every word occurs in each textual unit (or no row
returned for textual units where a particular word doesn't appear). I
used a group-by clause to group by "sectref", and then used the
COUNT(codelemm) function to sum up the occurrences. The codelemm
column had to be grouped on, in order to satisfy Postgres's
requirements. Here's the query as I have it:
> create table matrix2.tuo as select codelemm, sectref, count(codelemm)
from indethom group by codelemm, sectref;
And the explain results are as follows:
>it=> explain select codelemm, sectref, count(codelemm) from indethom
group by codelemm, sectref;
> QUERY PLAN
>-----------------------------------------------------------------------
---------
> GroupAggregate (cost=2339900.60..2444149.44 rows=1790528 width=13)
> -> Sort (cost=2339900.60..2364843.73 rows=9977252 width=13)
> Sort Key: codelemm, sectref
> -> Seq Scan on indethom (cost=0.00..455264.52 rows=9977252
width=13)
I have an index defined as follows:
> create index indethom_clemm_sect_ndx on indethom using
btree(codelemm, sectref);
I also performed an ANALYZE after creating the index.
I have the gut feeling that there's got to be a better way than a
sequence scan on 10,000,000 records, but I'll be darned if I can find
any way to improve things here.
Thanks for any help you all can offer!!
Erik Norvelle
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Slow performance with Group By
2004-11-09 00:09 Slow performance with Group By Erik Norvelle <signups@norvelle.org>
@ 2004-11-09 00:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-11-09 00:56 UTC (permalink / raw)
To: Erik Norvelle <signups@norvelle.org>; +Cc: pgsql-performance
Erik Norvelle <signups@norvelle.org> writes:
>>> it=> explain select codelemm, sectref, count(codelemm) from indethom
> group by codelemm, sectref;
>>> QUERY PLAN
>>> -----------------------------------------------------------------------
> ---------
>>> GroupAggregate (cost=2339900.60..2444149.44 rows=1790528 width=13)
>>> -> Sort (cost=2339900.60..2364843.73 rows=9977252 width=13)
>>> Sort Key: codelemm, sectref
>>> -> Seq Scan on indethom (cost=0.00..455264.52 rows=9977252
> width=13)
Actually the painful part of that is the sort. If you bump up sort_mem
enough it will eventually switch over to a HashAggregate with no sort,
which may be a better plan if there's not too many groups (is the
estimate of 1.79 million on the mark at all??)
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-11-09 00:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-09 00:09 Slow performance with Group By Erik Norvelle <signups@norvelle.org>
2004-11-09 00:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox