среда, 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...

понедельник, 12 ноября 2012 г.