pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Slow 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