From: Dave Dutcher <dave@tridecap.com>
To: 'smiley2211' <smiley2211@yahoo.com>
To: pgsql-performance@postgresql.org
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> (raw)
In-Reply-To: <12820410.post@talk.nabble.com>
References: <12803859.post@talk.nabble.com>
<55480.192.121.104.48.1190354307.squirrel@zigo.dhs.org>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dave@tridecap.com, smiley2211@yahoo.com
Subject: Re: Upgraded from 7.4 to 8.1.4 QUERIES NOW SLOW!!!
In-Reply-To: <01ba01c7fc58$cb346a40$cb00a8c0@tridecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox