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 <midg...@sbcglobal.net> >To: pgsql-performance@postgresql.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 > > >
