Re: Help Me Understand Why I'm Getting a Bad Query Plan

From: Tom Lane
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Date: ,
Msg-id: 11650.1238033714@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy)
Responses: Re: Help Me Understand Why I'm Getting a Bad Query Plan  (Bryan Murphy)
List: pgsql-performance


Bryan Murphy <> writes:
> I tried that already, but I decided to try again in case I messed up
> something last time.  Here's what I ran.  As you can see, it still
> chooses to do a sequential scan.  Am I changing the stats for those
> columns correctly?

I think what you should be doing is messing with the cost parameters
... and not in the direction you tried before.  I gather from
    effective_cache_size = 12GB
that you have plenty of RAM on this machine.  If the tables involved
are less than 1GB then it's likely that you are operating in a fully
cached condition, and the default cost parameters are not set up for
that.  You want to be charging a lot less for page accesses relative to
CPU effort.  Try reducing both seq_page_cost and random_page_cost to 0.5
or even 0.1.  You'll need to watch your other queries to make sure
nothing gets radically worse though ...

            regards, tom lane


pgsql-performance by date:

From: Mark Kirkwood
Date:
Subject: Re: Raid 10 chunksize
From: Dave Cramer
Date:
Subject: I have a fusion IO drive available for testing