Re: Weird 8.2.4 performance

Thank you everyone for the replies. I'll try to answer everyone's
questions in one post.

* Regarding production/mac memory and cache usage. This query HAS
been running on 8.0 on my Mac, I just got that particular query
explain from our production system because I had to nuke my local 8.0
database before installing 8.2.4 due to disk space limitations. The
query that this sample query is part of run in under 5 seconds when I
was running 8.0 locally on my mac, and it did a bunch of agregations
based on task instance.

* work_mem is set to 1 megabyte (the default) on both 8.0 and 8.2.4.

* setting enable_bitmapscan = false on 8.2.4

0605=# explain analyze select id from taskinstance where taskid in
(select id from task where campaignid = 76);

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-------
Nested Loop (cost=16.94..15484.61 rows=2309 width=4) (actual
time=44.751..8498.689 rows=1117 loops=1)
-> HashAggregate (cost=16.94..17.01 rows=7 width=4) (actual
time=0.144..0.194 rows=10 loops=1)
-> Index Scan using "Task_campaignId_key" on task
(cost=0.00..16.93 rows=7 width=4) (actual time=0.069..0.116 rows=10
loops=1)
Index Cond: (campaignid = 51)
-> Index Scan using taskid_taskinstance_key on taskinstance
(cost=0.00..2202.73 rows=554 width=8) (actual time=20.305..849.640
rows=112 loops=10)
Index Cond: (taskinstance.taskid = task.id)
Total runtime: 8499.599 ms

...FWIW, this query returns about 900 rows. TaskInstance is a fairly
large table in width (20 columns, about 15 are varchar, 3 timestamps
and a few ints)
and height (650,000) rows. I can't really run the same query
multiple times due to caching, so I change up "campaignid". Is there
a way to flush that cache? Turning off bitmap scans definitely seems
to help things, but I'm concerned that when/if I flip my production
machine, I'm going to run into who-knows-what. I don't really have a
set of SQL acceptance tests to test jumping from rev to rev (I know I
should- BAD DEVELOPER, BAD!).

* Configuration

- My production environment is running RedHat 2.6.9.ELsmp on a server
with 16GB of memory

- My old 8.0 database on my mac only had this modified from default:

shared_buffers = 100
work_mem = 1024

- 8.2.4 database seemed to go through some sort of auto-config when I
installed it, settings I think are different are as follows:

shared_buffers = 128MB # min 128kB or
max_connections*16kB
work_mem = 100MB # when I ran the original
query, this was set to 1MB, increased on Mark Kirkwood's advice,
seemed to help a bit but not really

8.2.4 Database size- 25 GB (from du -sh on the directory 'base')

* Richard Huxton

Thanks for the kind words- I'm glad I was able to 'ask a good
question'. I'm very new to this mailing list, but I'm on many Java/
Struts/Perl mailing lists and have seen enough poorly worded/spelled/
asked questions to last a lifetime. My situation is: I'm the senior
(read: first) developer at a small but growing startup. Everything I
know about PostgreSQL I've learned over the past 4 years in which our
tiny little DB grew from one database with 100 users to over a 4 node
Slony setup 300,000 users. Somehow, I'm not sure why, but I find
myself in the awkward position of being the 'go-to guy' for all
database related stuff at my company. What I don't know could fill
volumes, but I've been able to keep the durn database running for
over 4 years (which is mostly a testament to how awesome PostgreSQL
is)- so when I hit something that makes no sense, I KNOW that if I
have any hope of getting one of ye postgresql gods to help me with an
obscure, non-sensical problem such as this one, I'd better include as
much context as possible. :-) FWIW- we're looking to hire a
PostgreSQL hired gun to help me with this and many other things.
Ideally, that person would be in Boston, MA, USA and be able to come
into the office, but we'd consider remote people too. If you're
interested, drop me a line.

Thanks again for the replies, gang. Have there been many reported
performance related problems regarding people upgrading from 8.0->8.2?

Is there a primer somewhere on how to read EXPLAIN output?

Thanks again for helping me with this...

/kurt

On Jun 7, 2007, at 5:23 AM, Richard Huxton wrote:

> Mark Kirkwood wrote:
>> 8.2 is deciding to use a bitmap index scan on
>> taskid_taskinstance_key, which seems to be slower (!) than a plain
>> old index scan that 8.0 is using. A dirty work around is to
>> disable bitmap scans via:
>
> I'm having difficulty figuring out why it's doing this at all.
> There's only one index involved, and it's over the primary-key to
> boot!
>
> An EXPLAIN ANALYSE with enable_bitmapscan off should say why PG
> thinks the costs are cheaper than they actually are.
>
> PS - well worded question Kurt. All the relevant information neatly
> laid out, explain analyse on both platforms - you should be
> charging to let people help ;-)
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly