Re: Slow query: table iteration (8.3)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Feb 1, 2010 at 6:15 AM, Yeb Havinga <yhavinga@xxxxxxxxx> 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
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
   }

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux