Re: explain analyze

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Fri, 13 Sep 2002, Tourtounis Sotiris wrote:
>> select Y.at from table Y where Y.at >=1 and Y.at < 253214
>>
>> I have state an index on attribute at and by using the explain in front of
>> the select ... i get the information of an index scan-however i can't
>> distingusish whether that index scan refers to one of the two comparisons
>> or to the conjuction of those two - how i can see that?

This is one of the situations that I had in mind when I expanded
EXPLAIN's display for 7.3 ...

> 7.3's explain will show you the limiting clauses.  In past versions
> I'm not sure but possibly explain verbose would show it to you but that's
> just about unreadable.

You can figure it out from the EXPLAIN VERBOSE output if you are
determined.  I'd recommend looking at the pretty-printed form that
appears in the postmaster log, rather than the unformatted dump you get
in the NOTICE.  Look at the "indxqual" for the indexscan node, and see
how many operator invocations it contains.  If you want you can
correlate the operator OIDs to pg_operator to be sure of what you are
looking at.  For instance this is what "where unique1>0 and
unique1<20" looks like:

   :indxqual ((
      { EXPR
      :typeOid 16
      :opType op
      :oper
         { OPER
         :opno 521
         :opid 147
         :opresulttype 16
         }

      :args (
         { VAR
         :varno 1
         :varattno 1
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 1
         }

         { CONST
         :consttype 23
         :constlen 4
         :constbyval true
         :constisnull false
         :constvalue  4 [ 0 0 0 0 ]
         }
      )
      }

      { EXPR
      :typeOid 16
      :opType op
      :oper
         { OPER
         :opno 97
         :opid 66
         :opresulttype 16
         }

      :args (
         { VAR
         :varno 1
         :varattno 1
         :vartype 23
         :vartypmod -1
         :varlevelsup 0
         :varnoold 1
         :varoattno 1
         }

         { CONST
         :consttype 23
         :constlen 4
         :constbyval true
         :constisnull false
         :constvalue  4 [ 0 0 0 20 ]
         }
      )
      }
   )
   )


            regards, tom lane