Skip site navigation (1) Skip section navigation (2)

Re: Impact of foreign keys on a simple count(*) ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "C(dot) Bensend" <benny(at)bennyvision(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Impact of foreign keys on a simple count(*) ?
Date: 2004-10-09 03:49:23
Message-ID: 23596.1097293763@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"C. Bensend" <benny(at)bennyvision(dot)com> writes:
>    Is it the addition of the multiple foreign keys that is slowing this
> down so much?

Foreign keys have zero, nada, zilch to do with the performance of
count(*).  The only plausible theory I can think of for the performance
difference is that in your "new" database the table has been through
several mass updates, leading to a whole lot of dead rows and a much
larger physical table size to scan through.  I'd suggest a VACUUM
VERBOSE on both old and new copies of the table to get an idea of the
relative physical sizes.  You might need a VACUUM FULL to get the new
table back down to a reasonable size...

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: C. BensendDate: 2004-10-09 04:13:02
Subject: Re: Impact of foreign keys on a simple count(*) ?
Previous:From: C. BensendDate: 2004-10-09 03:37:33
Subject: Impact of foreign keys on a simple count(*) ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group