Re: Performance differences 7.1 to 7.3

I think my indexes are OK.

I have no settings for 'shared memory buffers' in postgresql.conf on
either system. Both systems have same, i.e.
#sort_mem = 512
#shared_buffers = 2*max_connections # min 16
#fsync = true

Do you, or any of you, have any other suggestions for why such a simple
"select count(*) from tpv;" should take six times as long on our latest
and greatest dual processor server with almost the latest and greatest
postgresql? View tpv is a three table join which takes 3.38 seconds on
the 'old' system and 18.09 seconds on the 'new' system.

Jim Apsey
----------------------------------------------------------------------------------------------------------------------------------
Dann Corbit wrote:

>-----Original Message-----
>From: Jimmie H. Apsey [mailto:japsey(at)futuredental(dot)com]
>Sent: Tuesday, December 14, 2004 1:18 PM
>To: Dann Corbit
>Cc: pgsql-general(at)postgresql(dot)org
>Subject: Re: [GENERAL] Performance differences 7.1 to 7.3
>
>On the 'old' Red Hat AS 2.1 here is the results of explain and the
>query:
>
>[ ~]$ time /usr/bin/psql mpt -c"explain select count(*) from tpv;"
>NOTICE: QUERY PLAN:
>
>Aggregate (cost=4563.87..4563.87 rows=1 width=56)
> -> Nested Loop (cost=870.92..4563.01 rows=342 width=56)
> -> Hash Join (cost=870.92..3869.17 rows=342 width=44)
> -> Seq Scan on treatment_plan (cost=0.00..956.66
>rows=14844 width=28)
> -> Hash (cost=602.33..602.33 rows=24033 width=16)
> -> Seq Scan on treatment_plan_header
>(cost=0.00..602.33 rows=24033 width=16)
> -> Index Scan using ada_code_pkey on ada_code (cost=0.00..2.01
>
>rows=1 width=12)
>
>EXPLAIN
>0.010u 0.000s 0:00.03 33.3% 0+0k 0+0io 332pf+0w
>[~]$ time /usr/bin/psql mpt -c"select count(*) from tpv;"
> count
>-------
> 33439
>(1 row)
>
>0.010u 0.000s 0:03.10 0.3% 0+0k 0+0io 332pf+0w
>[japsey(at)DCFRAID ~]$
>
>On the 'new' system:
>
>[ ~]$ /usr/bin/psql mpt -c"explain select count(*) from tpv;"
> QUERY
>PLAN
>------------------------------------------------------------------------
>---------------------------------------------------
> Aggregate (cost=202529.15..202529.15 rows=1 width=45)
> -> Nested Loop (cost=6262.46..202496.78 rows=12948 width=45)
> Join Filter: ("outer".service_code = ("inner".ada_code)::text)
> -> Merge Join (cost=6262.46..6754.54 rows=12933 width=36)
> Merge Cond: (("outer".appointment_order =
>"inner".appointment_order) AND ("outer".pat_id = "inner".pat_id))
> -> Sort (cost=2335.37..2395.35 rows=23992 width=14)
> Sort Key: treatment_plan_header.appointment_order,
>treatment_plan_header.pat_id
> -> Seq Scan on treatment_plan_header
>(cost=0.00..589.92 rows=23992 width=14)
> -> Sort (cost=3927.09..4016.27 rows=35672 width=22)
> Sort Key: treatment_plan.appointment_order,
>treatment_plan.pat_id
> -> Seq Scan on treatment_plan (cost=0.00..800.60
>rows=35672 width=22)
> Filter: (amount IS NULL)
> -> Seq Scan on ada_code (cost=0.00..10.06 rows=406 width=9)
>(13 rows)
>[ ~]$
>
>Where do I go to get clues about the results of "explain"?
>
>
>The command syntax:
>http://www.postgresql.org/docs/current/static/sql-explain.html
>A brief explanation:
>http://www.freebsddiary.org/postgresql-analyze.php
>A longer explanation:
>http://www.postgresql.org/docs/7.4/interactive/performance-tips.html
>A nuts and bolts look at how PostgreSQL performs queries:
>http://candle.pha.pa.us/main/writings/pgsql/performance.pdf
>
><<
>
>
>