Quick Links

Performance Issues with count()

From:	"asdf asdasfa" <sjg(at)email(dot)com>
To:	pgsql-general(at)postgresql(dot)org
Subject:	Performance Issues with count()
Date:	2002-04-23 22:06:54
Message-ID:	20020423220654.6998.qmail@email.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

<DIV>
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Ive found some performance issues with Postgres that Im hoping people on this list can help resolve.  Were working with a 65 million record table that includes year of birth (data type INT). To count the frequency of dates in the table, it takes 2 hours 26 minutes to execute. (Theres an approximately 100-year range of dates in the 65 million records).
<o:p> </o:p>
# EXPLAIN SELECT yb, count(1) FROM data_table GROUP BY yb;<o:p></o:p>
NOTICE:  QUERY PLAN:<o:p></o:p>
Aggregate (cost=16397434.27..16723548.69 rows=6522288 width=4)<o:p></o:p>
  -> Group (cost=16397434.27..16560491.48 rows=65222884 width=4)<o:p></o:p>
      -> Sort (cost=16397434.27..16397434.27 rows=65222884 width=4)<o:p></o:p>
          -> Seq Scan on data_table (cost=0.00..2368620.84 rows=65222884 width=4)<o:p></o:p>
<o:p> </o:p>
I can count data from the flat text data file with this Perl script:
<o:p> </o:p>
   #!/usr/bin/perl<o:p></o:p>
   # script to count YB frequencies in flat data file<o:p></o:p>
   open (IN, "$ARGV[0]");<o:p></o:p>
   open (OUT, ">$ARGV[0]\_cnt");<o:p></o:p>
   while (<IN>) {<o:p></o:p>
       chomp;<o:p></o:p>
       $years{$_}++;<o:p></o:p>
   }<o:p></o:p>
   foreach $key (keys %years) {<o:p></o:p>
       print OUT "$key,$years{$key}\n";<o:p></o:p>
   }<o:p></o:p>
<o:p> </o:p>
The Perl script takes *1 minute*, 31 seconds to run.  Why is there such a discrepancy in times?  Ive noticed that the Postgres count() function takes what seems to be longer than it should in other cases as well.  For instance, counting the frequency of last names in the same 65 million record table took *1 hour* and 31 minutes:
<o:p> </o:p>
# EXPLAIN SELECT ln, count(1) FROM data_table GROUP BY ln;<o:p></o:p>
NOTICE:  QUERY PLAN:<o:p></o:p>
Aggregate (cost=19538149.27..19864263.69 rows=6522288 width=19)<o:p></o:p>
  ->  Group (cost=19538149.27..19701206.48 rows=65222884 width=19)<o:p></o:p>
       ->  Sort (cost=19538149.27..19538149.27 rows=65222884 width=19)<o:p></o:p>
            ->  Seq Scan on data_table (cost=0.00..2324610.84 rows=65222884 width=19)<o:p></o:p>
<o:p> </o:p>
The last name (ln) and the year of birth (yb) is indexed, but that shouldnt matter because its doing a sequential scan, correct?  Am I running into the limitations of Postgres?  Wed like to eventually get this system into production, but if we cant get Postgres to count() faster, we may not be able to use it.
<o:p> </o:p>
Heres the data_table schema:
<o:p> </o:p>
# \d data_table<o:p></o:p>
         Table "data_table"<o:p></o:p>
 Column |     Type      | Modifiers <o:p></o:p>
--------+---------------+-----------<o:p></o:p>
 ss     | character(9)  | <o:p></o:p>
 ln     | character(15) | <o:p></o:p>
 fn     | character(15) | <o:p></o:p>
 mi     | character(1)  | <o:p></o:p>
 ns     | character(15) | <o:p></o:p>
 lny    | character(15) | <o:p></o:p>
 fny    | character(15) | <o:p></o:p>
 sny    | character(15) | <o:p></o:p>
 g      | character(1)  | <o:p></o:p>
 mb     | integer       | <o:p></o:p>
 db     | integer       | <o:p></o:p>
 yb     | integer       | <o:p></o:p>
 md     | integer       | <o:p></o:p>
 dd     | integer       | <o:p></o:p>
 yd     | integer       | <o:p></o:p>
Indexes: ssdi_ss_idx<o:p></o:p>
         ssdi_ln_idx<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Were working with Postgres v 7.2. The machine is a dual-processor Athlon MP1900 (Tyan Tiger board) with 3GB of PC2100 DDR RAM, and 3-80GB IBM 120GXP hard drives configured in a software RAID 0 Array running under RedHat Linux v. 7.2.
<o:p> </o:p>
Weve VACUUM ANALYZEd the tables after creating the indices.  Is there something Im missing here?
<o:p> </o:p>
Thanks for you suggestions.
 
Shaun Grannis</DIV>
--

<PRE>
_______________________________________________ 
Sign-up for your own FREE Personalized E-mail at <a href="http://www.email.com/?sr=signup" target="_new">Email.com</a>

</PRE>

Attachment	Content-Type	Size
unknown_filename	text/html	18.1 KB

Responses

Re: Performance Issues with count() at 2002-04-23 23:25:34 from Jan Wieck

Browse pgsql-general by date

	From	Date	Subject
Next Message	Robert J. Sanford, Jr.	2002-04-23 22:13:11	client authentication protocols?
Previous Message	Bruce Momjian	2002-04-23 20:58:41	Re: Returns from ExecTuplesOk()