From: "Dave Dutcher" <dave@tridecap.com>
To: "'smiley2211'" <smiley2211@yahoo.com>, <pgsql-performance@postgresql.org>
References: <12803859.post@talk.nabble.com>
	<55480.192.121.104.48.1190354307.squirrel@zigo.dhs.org>
	<12820410.post@talk.nabble.com>
Subject: Re: Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!
Date: Fri, 21 Sep 2007 09:07:49 -0500
Message-ID: <01ba01c7fc58$cb346a40$cb00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: Acf8Um2eugxz0sj5QuOC1yvWnekLmgAAvUrQ
In-Reply-To: <12820410.post@talk.nabble.com>


>From: smiley2211
>Subject: Re: [PERFORM] Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!
>
>->  Seq Scan on encounters_questions_answers eqa
>(cost=100000000.00..100007608.66 rows=464766 width=8) (actual
>time=0.003..735.934 rows=464766 loop
>s=7430)
                                                                          
It looks like enable_seqscan is set to false.  For some reason that might
have worked on 7.4, but I would try turning that back on for 8.1.
Sequential scans aren't always bad, sometimes they are faster than index
scans.  I would first try running the system with all the enable_* settings
on.

If you can't turn on logging its going to be very hard to track down the
problem.  The easiest way to track down a problem normally is to set
log_min_duration to something like 2000ms.  Then Postgres will log all slow
queries.  Then you can run EXPLAIN ANALYZE on the slow queries to find the
problem.

I think Carlos had a good idea when he asked about the encoding on the new
server vs the old.  Does your application use the like keyword to compare
text fields?  If so, you might need to create indexes which use the
text_pattern_ops operator classes.  With unicode postgres cannot use an
index scan for a query like SELECT * FROM foo WHERE name LIKE 'Bob%' unless
there is an index like CREATE INDEX name_index ON foo (name
text_pattern_ops).  However if you are not using like queries, then this is
not your problem.

More on operator classes:
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html

Dave