Re: Slow Query

On Thu, Aug 26, 2010 at 6:03 PM, Ozer, Pam <pozer@automotive.com> wrote:
>
> I am new to Postgres and I am trying to understand the Explain Analyze so I can tune the following query.  I run the
samequery using mysql and it takes less than 50ms.  I run it on postgres and it takes 10 seconds. I feel like I am
missingsomething very obvious. (VehicleUsed is a big table over 750,000records) and datasetgroupyearmakemodel has
150000records. 
>
> It looks like the cost is highest in the Hash Join  on Postalcode.   Am I reading this correctly.?  I do have indexes
onthe lower(postalcode) in both tables.  Why wouldn’t be using the index? 

No, it's spending most of its time here:



> "                          ->  Nested Loop  (cost=101.81..37776.78 rows=11887 width=10) (actual time=1.172..9876.586
rows=382528loops=1)" 

Note that it expects 11,887 rows but gets 382k rows.

Try turning up default stats target and running analyze again and see
how it runs.