Skip site navigation (1) Skip section navigation (2)

Re: slow query, different plans

From: "Midge Brown" <midgems(at)sbcglobal(dot)net>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow query, different plans
Date: 2012-08-06 18:36:43
Message-ID: 6553A486C90148479A9908834FD513DC@BERNICE (view raw or flat)
Thread:
Lists: pgsql-performance
----- Original Message ----- 
  From: Tom Lane 
  To: Midge Brown 
  Cc: pgsql-performance(at)postgresql(dot)org 
  Sent: Friday, August 03, 2012 11:26 PM
  Subject: Re: [PERFORM] slow query, different plans


  "Midge Brown" <midgems(at)sbcglobal(dot)net> writes:
  > 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. 

  Reindexing won't help that.  The problem is a bad statistical estimate;
  it thinks there are about 700 rows with applies2 = 256, when there's
  really only one.  That means the "fast" plan is a lot faster than the
  planner gives it credit for, and conversely the "slow" plan is a lot
  slower than the planner is expecting.  Their estimated costs end up
  nearly the same, which makes it a bit of a chance matter which one is
  picked --- but the true costs are a lot different.  So you need to fix
  that rowcount estimate.  Raising the stats target for the table might
  help.

  regards, tom lane

  -- 

  I added "and ts is not null" to the query and the planner came back with .102 ms. The problem area in production went from a 10 second response to < 1 second. 

  Thanks for the responses.

  -Midge

In response to

pgsql-performance by date

Next:From: Ioannis AnagnostopoulosDate: 2012-08-06 22:04:10
Subject: Re: Sequential scan instead of index scan
Previous:From: Midge BrownDate: 2012-08-06 17:43:13
Subject: Re: slow query, different plans

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group