Re: [SQL] using explain output within pgsql

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

 



Hello



2011/7/10 Uwe Bartels <uwe.bartels@xxxxxxxxx>:
> Hi,
>
> I'm starting up a datawarehouse with patitioning.
> my etl processes write directly into the corresponding partitions instead of
> using triggers.
>
> The reports I run in the datawarehouse are stored in a cache within the same
> database.
> Now I'd like to store besides the results the dependencies to the tables
> which were used to generate the report. with this information i could
> invalidate cache results for the tables I'm going to import with my etl
Hello

try

FOR l_explain IN EXPLAIN ANALYZE ...
LOOP
   ...

Regards

Pavel Stehule

> processes.
>
> explain analyze gives me the information which table or patition is read
> from for each report. e.g
> explain analyze (FORMAT YAML) create table cache.report234 as select
> col1,col2,sum(clicks) from dwh.event_log_weekly where week >= '2011-06-27'
> and week <= '2011-07-11' group by col1,col2;
>
> now I'd like to store the output of explain analyze in a pgsql variable for
> further processing. that looks something like this.
>
> DO $$declare l_explain text;
> begin
> l_explain := explain analyze (FORMAT YAML) create table cache.report234 as
> select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> '2011-06-27' and week <= '2011-07-11' group by col1,col2;
> select l_explain;
> end$$;
>
> But that doesn't work. I get a syntax error.
>
> Does anybody has an idea how to retrieve the output of explain within pgsql
> and store this in a variable?
> An alternative would be any other way to extract the information about
> tables used by arbitrary sql statements.
>
> best regards,
> Uwe
>

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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux