среда, 21 ноября 2012 г.

PostgreSQL quals in rewritten query plans

I've just examined PostgreSQL query debug output from the next query :
SELECT * from low_paid_emps_r;
where low_paid_emps_r is defined as
# SELECT definition from pg_views  where viewname like '%emps%';
                           definition     
-----------------------------------------------------
SELECT employees.employee_id, employees.first_name, employees.last_name, employees.email, employees.phone_number, employees.hire_date, employees.job_id, employees.salary, employees.commission_pct, employees.manager_id, employees.department_id FROM employees WHERE (employees.salary < (5000)::numeric);
In particular, I wished to find where is my where expression. It looks like the following.
   :quals 
               {OPEXPR 
               :opno 1754 
               :opfuncid 0 
               :opresulttype 16 
               :opretset false 
               :opcollid 0 
               :inputcollid 0 
               :args (
                  {VAR 
                  :varno 3 
                  :varattno 8 
                  :vartype 1700 
                  :vartypmod 524294 
                  :varcollid 0 
                  :varlevelsup 0 
                  :varnoold 3 
                  :varoattno 8 
                  :location -1
                  }
                  {FUNCEXPR 
                  :funcid 1740 
                  :funcresulttype 1700 
                  :funcretset false 
                  :funcformat 2 
                  :funccollid 0 
                  :inputcollid 0 
                  :args (
                     {CONST 
                     :consttype 23 
                     :consttypmod -1 
                     :constcollid 0 
                     :constlen 4 
                     :constbyval true 
                     :constisnull false 
                     :location -1 
                     :constvalue 4 [ -120 19 0 0 ]
                     }
                  )
                  :location -1
                  }
               )
               :location -1
               }
So, we have here <:
# SELECT oprname from pg_operator where oid=1754;
 oprname 
---------
<
(1 row)
The first argument is 8th attribute of our relation (salary). The other piece is ::numeric conversion:
# select proname from pg_proc where oid=1740;
 proname 
---------
 numeric
(1 row)
Ok, we reconstructed salary < numeric (Const) part. Let's find our constant.
# select typname from pg_type where oid=23;
 typname 
---------
 int4
(1 row)
It's int4. And constvalue as we can see from source code is a Datum. It seems form postgres.h that int4 is int32 and represented as:
#define DatumGetInt32(X) ((int32) GET_4_BYTES(X))
Now we can reconstruct out integer from ":constvalue 4 [ -120 19 0 0 ]" as 19*256+(256-120)=5000... Not evident; I possibly couldn't do this analysis not knowing original statement...

3 комментария:

  1. Does "Not evident; I possibly couldn't do this analysis not knowing original statement..." applies to the whole analyses or to some particular part of it?

    ОтветитьУдалить
  2. It applies to converting constvalue from Datum bits to something more understandable.

    ОтветитьУдалить
  3. Oh, come on, it's trivial, don't you know how integers are stored in memory?

    ОтветитьУдалить