From: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] using explain output within pgsql
Date: Sun, 10 Jul 2011 12:20:58 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.32-32-generic; KDE/4.4.5; i686; ; )
Cc: Uwe Bartels <uwe.bartels@gmail.com>,
 pgsql-admin@postgresql.org
References: 
 <CAPGEe=46ckcX+BCRnO7ayNf0abZQHe0i62_QLCAGj7oq0hKUzA@mail.gmail.com>
In-Reply-To: 
 <CAPGEe=46ckcX+BCRnO7ayNf0abZQHe0i62_QLCAGj7oq0hKUzA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201107101220.59167.adrian.klaver@gmail.com>

On Sunday, July 10, 2011 11:54:10 am Uwe Bartels wrote:
> Hi,
>=20
> I'm starting up a datawarehouse with patitioning.
> my etl processes write directly into the corresponding partitions instead
> of using triggers.
>=20
> 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
> processes.
>=20
> 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 >=3D '2011-06-=
27'
> and week <=3D '2011-07-11' group by col1,col2;
>=20
> now I'd like to store the output of explain analyze in a pgsql variable f=
or
> further processing. that looks something like this.
>=20
> DO $$declare l_explain text;
> begin
> l_explain :=3D explain analyze (FORMAT YAML) create table cache.report234=
 as
> select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=3D
> '2011-06-27' and week <=3D '2011-07-11' group by col1,col2;
> select l_explain;
> end$$;
>=20
> But that doesn't work. I get a syntax error.

=46rom here:
http://www.postgresql.org/docs/9.0/interactive/sql-explain.html

I believe you are looking for:
explain (ANALYZE, FORMAT YAML) create table...

>=20
> Does anybody has an idea how to retrieve the output of explain within pgs=
ql
> and store this in a variable?
> An alternative would be any other way to extract the information about
> tables used by arbitrary sql statements.
>=20
> best regards,
> Uwe

=2D-=20
Adrian Klaver
adrian.klaver@gmail.com