From: | Greg Williamson <gwilliamson39(at)yahoo(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: slow query, different plans |
Date: | 2012-08-04 01:30:56 |
Message-ID: | 1344043856.44633.YahooMailNeo@web125905.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Midge --
Sorry for top-quoting -- challenged mail.
Perhaps a difference in the stats estimates -- default_statistics_target ?
Can you show us a diff between the postgres config files for each instance ? Maybe something there ...
Greg Williamson
>________________________________
> From: Midge Brown <midgems(at)sbcglobal(dot)net>
>To: pgsql-performance(at)postgresql(dot)org
>Sent: Friday, August 3, 2012 5:38 PM
>Subject: [PERFORM] slow query, different plans
>
>
>
>I'm having a
problem with a query on our production server, but not on a laptop running a
similar postgres version with a recent backup copy of the same table. I tried reindexing the table on the production
server, but it didn't make any difference. Other queries on the same table are
plenty fast.
>
>This query has been slow, but never like this,
particularly during a period when there are only a couple of connections in use.
>
>Vacuum and analyze are run nightly (and
show as such in pg_stat_user_tables) in addition to autovacuum during
the day. Here are my autovacuum settings, but when I checked
last_autovacuum & last_autoanalyze in pg_stat_user_tables those fields
were blank.
>
>autovacuum =
on
>log_autovacuum_min_duration = 10
>autovacuum_max_workers =
3
>autovacuum_naptime =
1min
>autovacuum_vacuum_threshold = 50
>autovacuum_analyze_threshold = 50
>autovacuum_vacuum_scale_factor = 0.2
>autovacuum_analyze_scale_factor = 0.1
>autovacuum_freeze_max_age = 200000000
>autovacuum_vacuum_cost_delay = 10ms (changed earlier today from
1000ms)
>autovacuum_vacuum_cost_limit = -1
>
>wal_level = minimal
>wal_buffers = 16MB
>
>The only recent change was moving the 3 databases
we have from multiple raid 1 drives with tablespaces spread all over to one
large raid10 with indexes and data in pg_default. WAL for this table was moved
as well.
>
>Does anyone have any suggestions on where to look
for the problem?
>
>clientlog table info:
>
>Size: 1.94G
>
> Column
|
Type |
Modifiers
>----------+-----------------------------+-----------
> pid0
|
integer
| not null
> rid |
integer
| not null
> verb | character
varying(32) | not
null
> noun | character
varying(32) | not
null
> detail |
text
|
> path | character
varying(256) | not
null
> ts | timestamp without time
zone |
> applies2 |
integer
|
> toname | character
varying(128) |
> byname |
character varying(128) |
>Indexes:
> "clientlog_applies2" btree
(applies2)
> "clientlog_pid0_key" btree
(pid0)
> "clientlog_rid_key" btree
(rid)
> "clientlog_ts" btree (ts)
>
>The query, hardware info, and links to both
plans:
>
>explain analyze select max(ts) as ts from
clientlog where applies2=256;
>
>Production server:
>- 4 dual-core AMD Opteron 2212 processors,
2010.485 MHz
>- 64GB RAM
>- 464GB RAID10 drive
>- Linux 2.6.18-164.el5 #1
SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
> PostgreSQL 9.0.4 on
x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-46), 64-bit
>
>http://explain.depesz.com/s/8R4
>
>
>From laptop running Linux 2.6.34.9-69.fc13.868
with 3G ram against a copy of the same table:
>PostgreSQL 9.0.2 on i686-pc-linux-gnu, compiled
by GCC gcc (GCC) 4.4.4 20100630 (Red Hat 4.4.4-10), 32-bit
>
>http://explain.depesz.com/s/NQl
>
>Thank you,
>Midge
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-08-04 06:26:42 | Re: slow query, different plans |
Previous Message | Midge Brown | 2012-08-04 00:38:33 | slow query, different plans |