Thread: How to get fully qualified names with EXPLAIN
Hi All
Let's assume I've got 3 tables:
- "OrgStructure"."tblUnits",
- "OrgStructure"."tblUnitStructure",
- "Dictionary"."tblUnits"
I would like to do the EXPLAIN:
EXPLAIN
SELECT * FROM "OrgStructure"."tblUnits", "OrgStructure"."tblUnitStructure", "Dictionary"."tblUnits"
(Of course its cartesian product - doesn't matter)
I've got result:
Nested Loop (cost=0.00..971583.90 rows=77571000 width=482)
-> Nested Loop (cost=0.00..1930.03 rows=152100 width=354)
-> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390 width=177)
-> Materialize (cost=0.00..15.85 rows=390 width=177)
-> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390 width=177)
-> Materialize (cost=0.00..17.65 rows=510 width=128)
-> Seq Scan on "tblUnitStructure" (cost=0.00..15.10 rows=510 width=128)
My question is:
Which tblUnits is which one?
There are no fully qualified names in EXPLAIN output, so it looks impossible to determine information for particular table.
Is there any workaround, maybe I miss something?
any ideas, clues?
regards,
Bartek
Bartek
Hello 2013/6/27 Bartosz Dmytrak <bdmytrak@gmail.com>: > Hi All > Let's assume I've got 3 tables: > > "OrgStructure"."tblUnits", > "OrgStructure"."tblUnitStructure", > "Dictionary"."tblUnits" > > I would like to do the EXPLAIN: > EXPLAIN > SELECT * FROM "OrgStructure"."tblUnits", "OrgStructure"."tblUnitStructure", > "Dictionary"."tblUnits" > (Of course its cartesian product - doesn't matter) > > I've got result: > Nested Loop (cost=0.00..971583.90 rows=77571000 width=482) > -> Nested Loop (cost=0.00..1930.03 rows=152100 width=354) > -> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390 width=177) > -> Materialize (cost=0.00..15.85 rows=390 width=177) > -> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390 > width=177) > -> Materialize (cost=0.00..17.65 rows=510 width=128) > -> Seq Scan on "tblUnitStructure" (cost=0.00..15.10 rows=510 > width=128) > > My question is: > Which tblUnits is which one? > There are no fully qualified names in EXPLAIN output, so it looks impossible > to determine information for particular table. > > Is there any workaround, maybe I miss something? > any ideas, clues? pls, try EXPLAIN VERBOSE postgres=# explain select * from xx.omega; QUERY PLAN ───────────────────────────────────────────────────────── Seq Scan on omega (cost=0.00..34.00 rows=2400 width=4) (1 row) postgres=# explain verbose select * from xx.omega; QUERY PLAN ──────────────────────────────────────────────────────────── Seq Scan on xx.omega (cost=0.00..34.00 rows=2400 width=4) Output: a (2 rows) Regards Pavel Stehule > > > regards, > Bartek
Works like a charm :)
thanks a lot.
Regards,
Bartek
Bartek
2013/6/27 Pavel Stehule <pavel.stehule@gmail.com>
Hello
2013/6/27 Bartosz Dmytrak <bdmytrak@gmail.com>:pls, try EXPLAIN VERBOSE> Hi All
> Let's assume I've got 3 tables:
>
> "OrgStructure"."tblUnits",
> "OrgStructure"."tblUnitStructure",
> "Dictionary"."tblUnits"
>
> I would like to do the EXPLAIN:
> EXPLAIN
> SELECT * FROM "OrgStructure"."tblUnits", "OrgStructure"."tblUnitStructure",
> "Dictionary"."tblUnits"
> (Of course its cartesian product - doesn't matter)
>
> I've got result:
> Nested Loop (cost=0.00..971583.90 rows=77571000 width=482)
> -> Nested Loop (cost=0.00..1930.03 rows=152100 width=354)
> -> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390 width=177)
> -> Materialize (cost=0.00..15.85 rows=390 width=177)
> -> Seq Scan on "tblUnits" (cost=0.00..13.90 rows=390
> width=177)
> -> Materialize (cost=0.00..17.65 rows=510 width=128)
> -> Seq Scan on "tblUnitStructure" (cost=0.00..15.10 rows=510
> width=128)
>
> My question is:
> Which tblUnits is which one?
> There are no fully qualified names in EXPLAIN output, so it looks impossible
> to determine information for particular table.
>
> Is there any workaround, maybe I miss something?
> any ideas, clues?
postgres=# explain select * from xx.omega;
QUERY PLAN
─────────────────────────────────────────────────────────
Seq Scan on omega (cost=0.00..34.00 rows=2400 width=4)
(1 row)
postgres=# explain verbose select * from xx.omega;
QUERY PLAN
────────────────────────────────────────────────────────────
Seq Scan on xx.omega (cost=0.00..34.00 rows=2400 width=4)
Output: a
(2 rows)
Regards
Pavel Stehule
>
>
> regards,
> Bartek