From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martin Foster <martin@ethereal-realms.org>
Cc: PostgreSQL Novice <pgsql-novice@postgresql.org>
Subject: Re: Optimizer Parameters
Date: Sat, 12 Jul 2003 16:46:10 -0400
Message-ID: <18388.1058042770@sss.pgh.pa.us> (raw)
In-Reply-To: <3F0F6FBE.1090300@ethereal-realms.org>
References: <3F0F6FBE.1090300@ethereal-realms.org>
Martin Foster <martin@ethereal-realms.org> writes:
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, martin@ethereal-realms.org, pgsql-novice@postgresql.org
Subject: Re: Optimizer Parameters
In-Reply-To: <18388.1058042770@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox