From: | Yeb Havinga <yebhavinga(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: explain and PARAM_EXEC |
Date: | 2010-04-02 15:21:37 |
Message-ID: | 4BB60B81.1040200@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
> Anyway, I hadn't looked at your patch before, but now that I have, it's
> not even approximately what I was suggesting. What I thought you should
> do was change ruleutils.c to print the parameter expressions at the call
> site, ie in the T_SubPlan and T_AlternativeSubPlan cases in get_rule_expr.
>
See patch below against HEAD.
Example of query against catalog:
postgres=# explain verbose select oid::int + 1,(select oid from pg_class
a where a.oid = b.relfilenode and a.relnamespace = b.relnamespace) from
pg_class b;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on pg_catalog.pg_class b (cost=0.00..2459.64 rows=296 width=12)
Output: ((b.oid)::integer + 1), SubPlan 1 ($0 := b.relfilenode, $1 :=
b.relnamespace)
SubPlan 1
-> Index Scan using pg_class_oid_index on pg_catalog.pg_class a
(cost=0.00..8.27 rows=1 width=4)
Output: a.oid
Index Cond: (a.oid = $0)
Filter: (a.relnamespace = $1)
(7 rows)
Explain of alternative subplan (query comes from aggregates.sql in
regression).
regression=# explain verbose select ten, sum(distinct four) from onek a
group by ten
having exists (select 1 from onek b where sum(distinct a.four) = b.four);
QUERY
PLAN
--------------------------------------------------------------------------------------
GroupAggregate (cost=197.83..216.52 rows=10 width=8)
Output: a.ten, sum(DISTINCT a.four)
Filter: (alternatives: SubPlan 1 ($0 := sum(DISTINCT a.four)) or
hashed SubPlan 2)
-> Sort (cost=197.83..200.33 rows=1000 width=8)
Output: a.ten, a.four
Sort Key: a.ten
-> Seq Scan on public.onek a (cost=0.00..148.00 rows=1000
width=8)
Output: a.ten, a.four
SubPlan 1
-> Seq Scan on public.onek b (cost=0.00..150.50 rows=250 width=0)
Filter: ($0 = b.four)
SubPlan 2
-> Seq Scan on public.onek b (cost=0.00..148.00 rows=1000 width=4)
Output: b.four
(14 rows)
Would the explain above be better if the filter with subplans arguments
showed the EXISTS keyword? Much code from get_sublink_expr could be
reused to show additional info depending on the sublink type and testexpr.
Other info: there are now only appends to the context->buf. The problems
with OUTER var I mentioned earlier are gone now arguments are deparsed
using get_rule_expr instead of deparse_expression.
regards,
Yeb Havinga
Attachment | Content-Type | Size |
---|---|---|
subplanarguments.patch | text/plain | 3.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2010-04-02 15:21:57 | Re: Privs |
Previous Message | Tom Lane | 2010-04-02 14:56:57 | Re: xmlconcat (was 9.0 release notes done) |