To all,
The facts:
PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI
drives in hardware RAID 0 configuration. Database size with indexes is
currently 122GB. Schema for the table in question is at the end of this
email. The DB has been vacuumed full and analyzed. Between
Please show EXPLAIN ANALYZE output for your queries, not just EXPLAIN.
Also it would be useful to see the pg_stats rows for the date_key and
content_key columns.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analy
On Mon, 29 Dec 2003, Sean Shanny wrote:
> The first plan below has horrendous performance. we only get about 2%
> CPU usage and iostat shows 3-5 MB/sec IO. The second plan runs at 30%
> cpu and 15-30MB.sec IO.
>
> Could someone shed some light on why the huge difference in
> performance? B
I am running explain analyze now and will post results as they finish.
Thanks.
--sean
Tom Lane wrote:
Please show EXPLAIN ANALYZE output for your queries, not just EXPLAIN.
Also it would be useful to see the pg_stats rows for the date_key and
content_key columns.
regards, tom lane
Here is the pg_stats data. The explain analyze queries are still running.
select * from pg_stats where tablename = 'f_pageviews' and attname =
'date_key';
schemaname | tablename | attname | null_frac | avg_width |
n_distinct | most_common_vals
|
Here is one of the explain analyzes. This is the from the faster
query. Ignore the total runtime as we are currently doing other queries
on this machine so it is slightly loaded.
Thanks.
--sean
explain analyze select count (distinct (persistent_cookie_key) ) from
f_pageviews where date_key
Sean Shanny <[EMAIL PROTECTED]> writes:
> Here is the pg_stats data. The explain analyze queries are still running.
> select * from pg_stats where tablename = 'f_pageviews' and attname =
> 'content_key';
> schemaname | tablename | attname | null_frac | avg_width |
> n_distinct | most_com
Tom,
Thanks. I will make the changes you suggest concerning the indexes. I
am finding partial indexes to be very handy. :-)
I canceled the explain analyze on the other query as we have found the
problem and who knows how long it would take to complete.
Thanks again.
--sean
Tom Lane wrote:
I'm observing that when I have many processes doing some work on my
system that the transactions run along almost in lockstep. It appears
from messages posted here that the foreign keys are acquiring and
holding locks during the transactions, which seems like it would cause
this behavior.
I'd lik
