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

Re: Optimizer Parameters

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Optimizer Parameters
Date: 2003-07-13 00:16:29
Message-ID: 3F10A4DD.4050405@ethereal-realms.org (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-performance
Tom Lane wrote:
> 
>>force Postgres into using Indexes when available.    So I changed the 
>>following two lines in the .conf file:
> 
> 
>>    enable_seqscan = false
>>    enable_nestloop = false
> >This was recommended in the documentation,
> 
> 
> Where would you say that setting those off in the config file is
> "recommended"?
> 
> 
>>Now how sane is it to keep those options turned off?
> 
> 
> It isn't.  If you have to force them off for a particular query, do
> so right before you issue that query, and turn them on again after.
> Turning them off globally is sure to cause you pain later.
> 
> 
>>And any 
>>way to have the planner quiet guessing tens of thousands of rows will be 
>>return when there are at most hundred?
> 
> 
>>    AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')
>>    AND Po.PuppetName IS NOT NULL
> 
> 
>>                           ->  Seq Scan on post po  (cost=0.00..14369.84 rows=40513 width=41) (actual time=2820.88..2826.30 rows=392 loops=1)
>>                                 Filter: ((posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval)) AND (puppetname IS NOT NULL))
> 
> 
> Not with that coding technique; "LOCALTIMESTAMP - INTERVAL '10 minutes'"
> isn't a constant and so the planner can't look at its statistics to
> see that only a small part of the table will be selected.
> 
> There are two standard workarounds for this:
> 
> 1. Do the timestamp arithmetic on the client side, so that the query
> you send the backend has a simple constant:
> 
>     ... AND Po.PostTimestamp > '2003-07-12 16:27'
> 
> 2. Create a function that is falsely marked immutable, viz:
> 
> create function ago(interval) returns timestamp without time zone as
> 'select localtimestamp - $1' language sql immutable strict;
> 
>     ... AND Po.PostTimestamp > ago('10 minutes')
> 
> Because the function is marked immutable, the planner will reduce
> "ago('10 minutes')" to a constant on sight, and then use that value
> for planning purposes.  This technique can cause problems, since
> in some contexts the reduction will occur prematurely, but as long
> as you only use ago() in interactively-issued queries it works okay.
> 
> 			regards, tom lane

http://www.postgresql.org/docs/7.3/static/indexes-examine.html

The conf file does not make a mention of it, other then perhaps being 
used to debug.  The above link points to disabling it, but tells you 
nothing about potential consequences and what to do if it works better 
then it did before.

However, when I tried out your functions things started to work much 
better then previously.   This to say the least is a great sign as it 
will increase overall performance.

So thanks for that!   As a side note, would you recommend disabling 
fsync for added performance?   This would be joined with a healthy dose 
of a kernel file system buffer.

Simply curious, as I have been increasing certain options for the WAL to 
mean it writes less often (transactions are numerous so that's not an 
issue) to the hard drives.

	Martin Foster
	Creator/Designer Ethereal Realms
	martin(at)ethereal-realms(dot)org




In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2003-07-13 02:14:37
Subject: Re: [NOVICE] Optimizer Parameters
Previous:From: Ron JohnsonDate: 2003-07-12 21:13:30
Subject: Re: Problem with psql interface

pgsql-performance by date

Next:From: Tom LaneDate: 2003-07-13 02:14:37
Subject: Re: [NOVICE] Optimizer Parameters
Previous:From: Ron JohnsonDate: 2003-07-12 21:09:03
Subject: [OT] Such incredible h/w (was Re: Dual Xeon + HW RAIDquestion)

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