slow query, different plans
| От | Midge Brown |
|---|---|
| Тема | slow query, different plans |
| Дата | |
| Msg-id | 9CF5A680782142D794A085320949FED4@BERNICE обсуждение исходный текст |
| Ответы |
Re: slow query, different plans
Re: slow query, different plans |
| Список | pgsql-performance |
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
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)
----------+-----------------------------+-----------
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
- 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
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
Thank you,
Midge
В списке pgsql-performance по дате отправления: