Re: slow query, different plans

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
> 
>
>