To: Brian Wipf <brian@clickspace.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Same query - Slow in production 
In-reply-to: <18620DA0-DE55-4A2C-9DDC-BCF5F478E886@clickspace.com> 
References: <18620DA0-DE55-4A2C-9DDC-BCF5F478E886@clickspace.com>
Comments: In-reply-to Brian Wipf <brian@clickspace.com>
	message dated "Wed, 10 May 2006 16:39:14 -0600"
Date: Wed, 10 May 2006 21:20:05 -0400
Message-ID: <25818.1147310405@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Brian Wipf <brian@clickspace.com> writes:
> I'm trying to determine why an identical query is running  
> approximately 500 to 1000 times slower on our production database  
> compared to our backup database server.

It looks to me like it's pure luck that the query is fast on the backup
server.  The outer side of the EXISTS' join is being badly misestimated:

>             ->  Index Scan using  
> category_product__category_id_is_active_and_status_idx on  
> category_product cp  (cost=0.00..4362.64 rows=1103 width=4) (actual  
> time=0.013..0.015 rows=2 loops=5)
>                   Index Cond: ((category_id = $1) AND  
> ((product_is_active)::text = 'true'::text) AND  
> ((product_status_code)::text = 'complete'::text))

If there actually had been 1100 matching rows instead of 2, the query
would have run 550 times slower, putting it in the same ballpark as
the other plan.  So what I'm guessing is that the planner sees these
two plans as being nearly the same cost, and small differences in the
stats between the two databases are enough to tip its choice in one
direction or the other.

So what you want, of course, is to improve that rowcount estimate.
I suppose the reason it's so bad is that we don't have multicolumn
statistics ... is there a strong correlation between product_is_active
and product_status_code?  If so, it might be worth your while to find a
way to merge them into one column.

			regards, tom lane