pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer Parameters
7+ messages / 5 participants
[nested] [flat]
* Optimizer Parameters
@ 2003-07-12 02:17 Martin Foster <martin@ethereal-realms.org>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Foster @ 2003-07-12 02:17 UTC (permalink / raw)
To: PostgreSQL Novice <pgsql-novice@postgresql.org>
Upon testing queries with EXPLAIN ANALYSE, I started to notice that the
planner would avoid using indexes when available. Instead it would
jump to sequence scans, ignoring the index and increasing overall time
it took to get results.
I have been looking up documentation and noticed that you can somewhat
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, and to say the least things
have really changed in performance. Queries have halved the time
needed to execute even if the estimates are insanely high compared.
I also increased this value, which apparently helps when running ANALYSE
on tables:
default_statistics_target = 1000
Now how sane is it to keep those options turned off? And what side
effects can I expect from changing default_statistics_target? And any
way to have the planner quiet guessing tens of thousands of rows will be
return when there are at most hundred?
I included the EXPLAIN ALALYSE results in an attachment to maintain
formatting of the output. Thanks in advance!
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org
Attachments:
planner.txt (text/plain, 9.0K) download
EXPLAIN ANALYSE SELECT
Po.PuppetName AS PuppetName,
Po.PuppeteerLogin AS PuppeteerLogin,
Po.RealmName AS RealmName,
Re.RealmPublic AS RealmPublic,
Re.RealmVerified AS RealmVerified
FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re
WHERE Po.PuppeteerLogin = Ch.PuppeteerLogin
AND Po.RealmName = Re.RealmName
AND Po.PostTimestamp > (LOCALTIMESTAMP - INTERVAL '10 minutes')
AND Po.PuppetName IS NOT NULL
GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified;
-- Pre changes
Group (cost=298025.66..322310.42 rows=161898 width=77) (actual time=4241.28..4329.68 rows=74 loops=1)
-> Sort (cost=298025.66..302073.12 rows=1618985 width=77) (actual time=4241.23..4253.68 rows=14420 loops=1)
Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
-> Merge Join (cost=24174.23..44794.94 rows=1618985 width=77) (actual time=3199.66..3891.09 rows=14420 loops=1)
Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
-> Sort (cost=5964.67..6056.93 rows=36906 width=10) (actual time=366.18..427.94 rows=36318 loops=1)
Sort Key: ch.puppeteerlogin
-> Seq Scan on puppet ch (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..133.52 rows=36456 loops=1)
-> Sort (cost=18209.57..18310.85 rows=40513 width=67) (actual time=2832.70..2945.77 rows=14411 loops=1)
Sort Key: po.puppeteerlogin
-> Hash Join (cost=30.30..15109.11 rows=40513 width=67) (actual time=2822.23..2830.93 rows=392 loops=1)
Hash Cond: ("outer".realmname = "inner".realmname)
-> 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))
-> Hash (cost=29.84..29.84 rows=184 width=26) (actual time=1.25..1.25 rows=0 loops=1)
-> Seq Scan on realm re (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.90 rows=179 loops=1)
Total runtime: 4419.10 msec
(17 rows)
-- Post changes
Group (cost=100293106.79..100316406.96 rows=155334 width=77) (actual time=1029.10..1159.25 rows=99 loops=1)
-> Sort (cost=100293106.79..100296990.15 rows=1553344 width=77) (actual time=1025.98..1047.32 rows=24730 loops=1)
Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
-> Merge Join (cost=100031106.45..100050913.48 rows=1553344 width=77) (actual time=453.60..839.30 rows=24730 loops=1)
Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
-> Sort (cost=25124.25..25229.58 rows=42133 width=67) (actual time=17.07..17.67 rows=631 loops=1)
Sort Key: po.puppeteerlogin
-> Hash Join (cost=120.06..21887.85 rows=42133 width=67) (actual time=2.32..14.25 rows=631 loops=1)
Hash Cond: ("outer".realmname = "inner".realmname)
-> Index Scan using idxpost_timestamp on post po (cost=0.00..21030.46 rows=42133 width=41) (actual time=0.15..7.05 rows=631 loops=1)
Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
Filter: (puppetname IS NOT NULL)
-> Hash (cost=119.61..119.61 rows=181 width=26) (actual time=2.01..2.01 rows=0 loops=1)
-> Index Scan using pkrealm on realm re (cost=0.00..119.61 rows=181 width=26) (actual time=0.05..1.55 rows=181 loops=1)
-> Sort (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=435.39..506.81 rows=58859 loops=1)
Sort Key: ch.puppeteerlogin
-> Seq Scan on puppet ch (cost=100000000.00..100003222.28 rows=36428 width=10) (actual time=0.03..184.55 rows=36428 loops=1)
Total runtime: 1175.33 msec
(21 rows)
EXPLAIN ANALYSE SELECT
Po.PuppetName AS PuppetName,
Po.PuppeteerLogin AS PuppeteerLogin,
Po.RealmName AS RealmName,
Re.RealmPublic AS RealmPublic,
Re.RealmVerified AS RealmVerified
FROM ethereal.Post Po, ethereal.Puppet Ch, ethereal.Realm Re
WHERE Po.PostIDNumber > (SELECT MIN(PostIDNumber)
FROM ethereal.Post
WHERE PostTimeStamp > (LOCALTIMESTAMP - INTERVAL '10 minutes'))
AND Po.PuppeteerLogin = Ch.PuppeteerLogin
AND Po.RealmName = Re.RealmName
AND Po.PuppetName IS NOT NULL
GROUP BY Po.PuppeteerLogin, Po.PuppetName, Po.RealmName, Re.RealmPublic, Re.RealmVerified;
-- Pre changes
Group (cost=297096.47..321381.23 rows=161898 width=77) (actual time=2108.70..2179.12 rows=76 loops=1)
InitPlan
-> Aggregate (cost=14473.08..14473.08 rows=1 width=4) (actual time=1141.58..1141.58 rows=1 loops=1)
-> Seq Scan on post (cost=0.00..14369.84 rows=41297 width=4) (actual time=1136.50..1140.95 rows=413 loops=1)
Filter: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
-> Sort (cost=297096.47..301143.93 rows=1618985 width=77) (actual time=2108.55..2121.85 rows=15302 loops=1)
Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
-> Merge Join (cost=23245.04..43865.75 rows=1618985 width=77) (actual time=1782.43..2003.58 rows=15302 loops=1)
Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
-> Sort (cost=5964.67..6056.93 rows=36906 width=10) (actual time=415.56..449.62 rows=36318 loops=1)
Sort Key: ch.puppeteerlogin
-> Seq Scan on puppet ch (cost=0.00..3165.06 rows=36906 width=10) (actual time=0.02..135.58 rows=36456 loops=1)
-> Sort (cost=17280.38..17381.66 rows=40513 width=67) (actual time=1362.15..1376.53 rows=15293 loops=1)
Sort Key: po.puppeteerlogin
-> Hash Join (cost=30.30..14179.92 rows=40513 width=67) (actual time=1354.41..1360.42 rows=411 loops=1)
Hash Cond: ("outer".realmname = "inner".realmname)
-> Seq Scan on post po (cost=0.00..13440.65 rows=40513 width=41) (actual time=1353.11..1355.96 rows=411 loops=1)
Filter: ((postidnumber > $0) AND (puppetname IS NOT NULL))
-> Hash (cost=29.84..29.84 rows=184 width=26) (actual time=1.20..1.20 rows=0 loops=1)
-> Seq Scan on realm re (cost=0.00..29.84 rows=184 width=26) (actual time=0.02..0.88 rows=179 loops=1)
Total runtime: 2192.72 msec
(21 rows)
-- Post changes
Group (cost=100292759.45..100316059.61 rows=155334 width=77) (actual time=876.30..969.77 rows=84 loops=1)
InitPlan
-> Aggregate (cost=21137.57..21137.57 rows=1 width=4) (actual time=3.98..3.98 rows=1 loops=1)
-> Index Scan using idxpost_timestamp on post (cost=0.00..21030.46 rows=42846 width=4) (actual time=0.14..3.26 rows=538 loops=1)
Index Cond: (posttimestamp > (('now'::text)::timestamp(6) without time zone - '00:10'::interval))
-> Sort (cost=100292759.45..100296642.81 rows=1553344 width=77) (actual time=875.32..893.11 rows=20378 loops=1)
Sort Key: po.puppeteerlogin, po.puppetname, po.realmname, re.realmpublic, re.realmverified
-> Merge Join (cost=100030759.11..100050566.13 rows=1553344 width=77) (actual time=436.22..708.88 rows=20378 loops=1)
Merge Cond: ("outer".puppeteerlogin = "inner".puppeteerlogin)
-> Sort (cost=24776.90..24882.24 rows=42133 width=67) (actual time=26.54..27.01 rows=524 loops=1)
Sort Key: po.puppeteerlogin
-> Hash Join (cost=120.06..21540.50 rows=42133 width=67) (actual time=5.93..24.30 rows=524 loops=1)
Hash Cond: ("outer".realmname = "inner".realmname)
-> Index Scan using pkpost on post po (cost=0.00..20683.11 rows=42133 width=41) (actual time=4.08..17.10 rows=524 loops=1)
Index Cond: (postidnumber > $0)
Filter: (puppetname IS NOT NULL)
-> Hash (cost=119.61..119.61 rows=181 width=26) (actual time=1.68..1.68 rows=0 loops=1)
-> Index Scan using pkrealm on realm re (cost=0.00..119.61 rows=181 width=26) (actual time=0.04..1.28 rows=181 loops=1)
-> Sort (cost=100005982.20..100006073.27 rows=36428 width=10) (actual time=408.56..456.91 rows=54202 loops=1)
Sort Key: ch.puppeteerlogin
-> Seq Scan on puppet ch (cost=100000000.00..100003222.28 rows=36428 width=10) (actual time=0.01..162.22 rows=36428 loops=1)
Total runtime: 984.75 msec
(22 rows)
* Re: Optimizer Parameters
@ 2003-07-12 20:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Foster <martin@ethereal-realms.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-07-12 20:46 UTC (permalink / raw)
To: Martin Foster <martin@ethereal-realms.org>; +Cc: PostgreSQL Novice <pgsql-novice@postgresql.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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Optimizer Parameters
@ 2003-07-13 00:16 Martin Foster <martin@ethereal-realms.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Martin Foster @ 2003-07-13 00:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-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@ethereal-realms.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [NOVICE] Optimizer Parameters
@ 2003-07-13 02:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Martin Foster <martin@ethereal-realms.org>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-07-13 02:14 UTC (permalink / raw)
To: Martin Foster <martin@ethereal-realms.org>; +Cc: pgsql-performance
Martin Foster <martin@ethereal-realms.org> writes:
> As a side note, would you recommend disabling
> fsync for added performance?
Only if you are willing to sacrifice crash-safety in the name of speed.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* optimizer parameters
@ 2011-04-10 18:22 Radhya sahal <rad_cs_2006@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Radhya sahal @ 2011-04-10 18:22 UTC (permalink / raw)
To: pgsql-performance
Dear ,all
i would to ask you about how postgresql optimizer parameters :-
1- random page cost
2- cpu tuple cost3- cpu operator cost4- cpu index tuple cost5- effective cache
size
play as parameters in cost estimator equation
i imagine that cost function is the same as
f(x,y,z,....)=ax+by......
cost(cpu tuple cost,cpu operator cost,....)
can any one help me to know the equation that cost estimator used it..
My regard
Radhya,,,,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimizer parameters
@ 2011-04-10 23:27 Tomas Vondra <tv@fuzzy.cz>
parent: Radhya sahal <rad_cs_2006@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tomas Vondra @ 2011-04-10 23:27 UTC (permalink / raw)
To: pgsql-performance
There's a quite nice description in the docs:
http://www.postgresql.org/docs/9.0/interactive/row-estimation-examples.html
and a some more details for indexes:
http://www.postgresql.org/docs/9.0/interactive/index-cost-estimation.html
A bit more info about how this is used is available in this presentation:
http://momjian.us/main/writings/pgsql/internalpics.pdf
But if you need more details, then I quess the best approach to get it
is to read the sources (search for the cost estimation etc.).
regards
Tomas
Dne 10.4.2011 20:22, Radhya sahal napsal(a):
> Dear ,all
> i would to ask you about how postgresql optimizer parameters :-
> 1- random page cost
>
> 2- cpu tuple cost
>
> 3- cpu operator cost
>
> 4- cpu index tuple cost
>
> 5- effective cache size
>
> play as parameters in cost estimator equation
>
> i imagine that cost function is the same as
>
> f(x,y,z,....)=ax+by......
>
> cost(cpu tuple cost,cpu operator cost,....)
>
> can any one help me to know the equation that cost estimator used it..
>
> My regard
>
> Radhya,,,,
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: optimizer parameters
@ 2011-04-11 02:40 Greg Smith <greg@2ndQuadrant.com>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 7+ messages in thread
From: Greg Smith @ 2011-04-11 02:40 UTC (permalink / raw)
To: pgsql-performance
On 04/10/2011 07:27 PM, Tomas Vondra wrote:
> But if you need more details, then I quess the best approach to get it
> is to read the sources (search for the cost estimation etc.).
>
There's a small fully worked out example of this in my book too, where I
duplicate the optimizer's EXPLAIN cost computations for a simple query.
The main subtle thing most people don't appreciate fully is how much the
optimizer takes into account two things: the selectivity of operators,
and the expected size of the data being moved around. For example, a
lot of the confusion around "why didn't it use my index?" comes from not
noticing the size of the index involved, and therefore how expensive its
page cost is considered to be.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-04-11 02:40 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-12 02:17 Optimizer Parameters Martin Foster <martin@ethereal-realms.org>
2003-07-12 20:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-13 00:16 ` Martin Foster <martin@ethereal-realms.org>
2003-07-13 02:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-10 18:22 optimizer parameters Radhya sahal <rad_cs_2006@yahoo.com>
2011-04-10 23:27 ` Re: optimizer parameters Tomas Vondra <tv@fuzzy.cz>
2011-04-11 02:40 ` Re: optimizer parameters Greg Smith <greg@2ndQuadrant.com>
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