From: Glenn Maynard <glenn@zewt.org>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query: table iteration (8.3)
Date: Mon, 1 Feb 2010 21:52:45 -0500
Message-ID: <bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com> (raw)
In-Reply-To: <4B66B7E0.3020605@gmail.com>
References: <bd36f99e1001291949r2e769e8i7050a832b8d5a3ef@mail.gmail.com>
<4B66B7E0.3020605@gmail.com>
On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@gmail.com> wrote:
> Glenn Maynard wrote:
>> SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
>> Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4)
>> (actual time=26509.919..26509.919 rows=0 loops=1)
>> Total runtime: 26509.972 ms
> Stomp_steps is analyzed to 2902 rows but when you run the query the actual
> rows are 0. This means that the highscore function is not called or the
> number 0 is incorrect.
This SELECT returns 0 rows: it calls the function 1500 times, and each
time it returns no data, because there simply aren't any results for
these parameters.
> below. The truth might be that you probably got that result by explaining
> the query in the function with actual parameter values. This plan differs
> from the one that is made when the function is called from sql and is
> planned (once) without parameters, and in that case the plan is probably
> different.
Yeah. It would help a lot if EXPLAIN could show query plans of
functions used by the statement and not just the top-level query.
> A way to check the plan of that query is to turn on
> debug_print_plan and watch the server log. It takes a bit getting used. The
> plan starts with CONTEXT: SQL function "functionname" during startup and is
> also recognized because in the opexpr (operator expression) one of the
> operands is a parameter. Important is the total cost of the top plan node
> (the limit).
Thanks.
"SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s":
Squinting at the output, it definitely looks like a less optimized
plan; it's using a SEQSCAN instead of BITMAPHEAPSCAN. (I've attached
the output.)
Does the planner not optimize functions based on context? That seems
like a huge class of optimizations. The first NULLTEST can be
optimized away, since that parameter comes from a NOT NULL source (a
PK). The second NULLTEST can also be optimized away, since it's a
constant value (591). The search could be a BITMAPHEAPSCAN,
substituting the s.id value for each call, instead of a SEQSCAN. (Not
that I'm concerned about a few cheap NULLTESTs, I'm just surprised at
it using such a generic plan.)
If I create a new function with the constant parameters hard-coded,
it's back to BITMAPHEAPSCAN: 175ms. This suggests a horrible
workaround: creating temporary functions every time I make this type
of query, with the fixed values substituted textually. I'd really
love to know a less awful fix.
> I know 8.3 is mentioned in the subject, but I think that a WITH query
> (http://www.postgresql.org/docs/8.4/interactive/queries-with.html) could be
> a good solution to your problem and may be worth trying out, if you have the
> possibility to try out 8.4.
I can't see how to apply WITH to this. Non-recursive WITH seems like
syntax sugar that doesn't do anything a plain SELECT can't do, and I
don't think what I'm doing here can be done with a regular SELECT.
--
Glenn Maynard
Attachments:debug.txt (text/plain, 9.4K) download
SELECT highscores_for_steps_and_card(s.id, 591, 1) FROM stomp_steps s;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Seq Scan on stomp_steps s (cost=0.00..793.52 rows=2902 width=4) (actual time=1726.998..26729.717 rows=17 loops=1)
Total runtime: 26729.822 ms
DEBUG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:canSetTag true
:planTree
{SEQSCAN
:startup_cost 0.00
:total_cost 793.52
:plan_rows 2902
:plan_width 4
:targetlist (
{TARGETENTRY
:expr
{FUNCEXPR
:funcid 240532
:funcresulttype 23
:funcretset true
:funcformat 0
:args (
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
{CONST
:consttype 23
:consttypmod -1
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 79 2 0 0 ]
}
{CONST
:consttype 23
:consttypmod -1
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 1 0 0 0 ]
}
)
}
:resno 1
:resname highscores_for_steps_and_card
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual <>
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:rtable (
{RTE
:alias
{ALIAS
:aliasname s
:colnames <>
}
:eref
{ALIAS
:aliasname s
:colnames ("id" ...)
}
:rtekind 0
:relid 240100
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
}
)
:resultRelations <>
:utilityStmt <>
:intoClause <>
:subplans <>
:rewindPlanIDs (b)
:returningLists <>
:rowMarks <>
:relationOids (o 240100)
:nParamExec 0
}
DEBUG: plan:
DETAIL: {PLANNEDSTMT
:commandType 1
:canSetTag true
:planTree
{LIMIT
:startup_cost 615.59
:total_cost 615.60
:plan_rows 1
:plan_width 8
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 240375
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 700
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resno 2
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk true
}
)
:qual <>
:lefttree
{SORT
:startup_cost 615.59
:total_cost 615.60
:plan_rows 1
:plan_width 8
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 240375
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 65001
:varattno 2
:vartype 700
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resno 2
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk true
}
)
:qual <>
:lefttree
{SEQSCAN
:startup_cost 0.00
:total_cost 615.58
:plan_rows 1
:plan_width 8
:targetlist (
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
:resno 1
:resname id
:ressortgroupref 0
:resorigtbl 240375
:resorigcol 1
:resjunk false
}
{TARGETENTRY
:expr
{VAR
:varno 1
:varattno 14
:vartype 700
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 14
}
:resno 2
:resname <>
:ressortgroupref 1
:resorigtbl 0
:resorigcol 0
:resjunk true
}
)
:qual (
{BOOLEXPR
:boolop or
:args (
{NULLTEST
:arg
{PARAM
:paramkind 0
:paramid 1
:paramtype 23
:paramtypmod -1
}
:nulltesttype 0
}
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 10
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 10
}
{PARAM
:paramkind 0
:paramid 1
:paramtype 23
:paramtypmod -1
}
)
}
)
}
{BOOLEXPR
:boolop or
:args (
{NULLTEST
:arg
{PARAM
:paramkind 0
:paramid 2
:paramtype 23
:paramtypmod -1
}
:nulltesttype 0
}
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
{PARAM
:paramkind 0
:paramid 2
:paramtype 23
:paramtypmod -1
}
)
}
)
}
)
:lefttree <>
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:scanrelid 1
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:numCols 1
:sortColIdx 2
:sortOperators 623
:nullsFirst true
}
:righttree <>
:initPlan <>
:extParam (b)
:allParam (b)
:limitOffset <>
:limitCount
{FUNCEXPR
:funcid 481
:funcresulttype 20
:funcretset false
:funcformat 2
:args (
{PARAM
:paramkind 0
:paramid 3
:paramtype 23
:paramtypmod -1
}
)
}
}
:rtable (
{RTE
:alias
{ALIAS
:aliasname r
:colnames <>
}
:eref
{ALIAS
:aliasname r
:colnames ("id" "steps_id" ...)
}
:rtekind 0
:relid 240375
:inh false
:inFromCl true
:requiredPerms 2
:checkAsUser 0
}
)
:resultRelations <>
:utilityStmt <>
:intoClause <>
:subplans <>
:rewindPlanIDs (b)
:returningLists <>
:rowMarks <>
:relationOids (o 240375)
:nParamExec 0
}
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: glenn@zewt.org
Subject: Re: Slow query: table iteration (8.3)
In-Reply-To: <bd36f99e1002011852j19a34de7qfbd860f2a97584c@mail.gmail.com>
* 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