Thread: Planner debug views
Here is a proposal introducing some debugging infrastructure into the
core. The basic idea is to allow us to query the planner search space.
To so do, we can dump related information to csv files and use foreign
table to query them. So here I propose two foreign tables:
create foreign table pg_planner_rels(rel text, content text) ... create foreign table pg_planner_paths(rel text, path
text,
replacedby text, totalcost float, cheapest text, content text) ...
Where
1. pg_planner_rels consists of RelOptInfo information.
2. pg_planner_paths consists of paths belong to each rel.
3. Field "rel" is a RelOptInfo* pointer so these two tables can join each other.
4. We can further adding subqueries view, or expand pg_planner_paths
table easily adding startup_cost etc.
And an example output is attached. The nice thing about it, is it
reusing the database query power to diagnose database query problems
:-). From the tables, we can find out all paths considered and why
they are discarded.
I have a draft implementation of above idea - main changes including:
1. Keep removed path in a separate list, also the reason of removal
(see "replacedby" field);
2. Change debug_print_rel() family to use StringInfo instead of printf().
I would also suggest we change DEBUG_OPTIMIZER into a GUC, because we
need this ability in release build as well. There is almost no
overhead when GUC is off.
Before I get into more details, I'd like to see if we see this is a
good way to go.
Thoughts?
Regards,
Qingqing
-- query to test
postgres=# explain select a.i, count(a.i) from a join b on a.i=b.i
group by a.i order by a.i limit 1; QUERY PLAN
--------------------------------------------------------------------------------------Limit (cost=14.47..15.64 rows=1
width=4) -> GroupAggregate (cost=14.47..120.67 rows=91 width=4) Group Key: a.i -> Merge Join
(cost=14.47..97.51rows=4450 width=4) Merge Cond: (a.i = b.i) -> Index Only Scan using ai on
a (cost=0.28..45.07
rows=600 width=4) -> Sort (cost=14.20..14.72 rows=210 width=4) Sort Key: b.i
-> Seq Scan on b (cost=0.00..6.10 rows=210 width=4)
(9 rows)
-- after query is done we can see rels
postgres=# select * from pg_planner_rels; rel | content
---------+-------------------------------------2f49b70 | RELOPTINFO (1): rows=600 width=42f49d08 | RELOPTINFO (2):
rows=210width=42f4a590 | RELOPTINFO (1 2): rows=4450 width=41543340 | RELOPTINFO (1): rows=1 width=64
(4 rows)
-- and paths
postgres=# select rel, path, replacedby, totalcost, substr(content, 1,
30) from pg_planner_paths ; rel | path | replacedby | totalcost | substr
---------+---------+------------+-----------+--------------------------------2f49b70 | 2f4abb8 | | 1
|IdxScan(1) rows=600 cost=0.28.2f49b70 | 2f4a1f0 | | 10 | SeqScan(1) rows=600 cost=0.00.2f49b70 |
2f49e10| | 45 | IdxScan(1) rows=600 cost=0.28.2f49b70 | 2f4d6b8 | 2f4d6b8 | 5 |
BitmapHeapScan(1)rows=600 cos2f49d08 | 2f4e3e0 | | 6 | SeqScan(2) rows=210 cost=0.00.2f4a590 |
2f4f938| | 77 | HashJoin(1 2) rows=4450 cost=12f4a590 | 2f4f678 | | 88 | HashJoin(1
2)rows=4450 cost=82f4a590 | 2f4f330 | | 98 | MergeJoin(1 2) rows=4450 cost=2f4a590 | 2f4f850 |
| 140 | NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f188 | | 1907 | NestLoop(1 2) rows=4450
cost=02f4a590| 2f4f278 | | 1942 | NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f8a8 | 2f4f8a8 |
1908| NestLoop(1 2) rows=4450 cost=02f4a590 | 2f4f700 | 2f4f700 | 105 | MergeJoin(1 2) rows=4450 cost=2f4a590
|2f4eea0 | 2f4f330 | 120 | MergeJoin(1 2) rows=4450 cost=2f4a590 | 2f4f220 | 2f4f278 | 5280 |
NestLoop(12) rows=4450 cost=02f4a590 | 2f4efb8 | 2f4f188 | 5245 | NestLoop(1 2) rows=4450 cost=01543340 |
1543d08| | 1 | ForeignScan(1) rows=1 cost=0.01543cf8 | 2f49a20 | | 3 |
ForeignScan(1)rows=17 cost=0.
(18 rows)
On Tue, Jul 21, 2015 at 5:15 PM, Qingqing Zhou <zhouqq.postgres@gmail.com> wrote: > Here is a proposal introducing some debugging infrastructure into the > core. The basic idea is to allow us to query the planner search space. > To so do, we can dump related information to csv files and use foreign > table to query them. FWIW, this is to tackle(+) optimizer TODO item: https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor Improve ability to display optimizer analysis using OPTIMIZER_DEBUG Regards, Qingqing
> Here is a proposal introducing some debugging infrastructure into the > core. The basic idea is to allow us to query the planner search space. > To so do, we can dump related information to csv files and use foreign > table to query them. So here I propose two foreign tables: Sounds like a great feature! Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
On Thu, Jul 23, 2015 at 4:11 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
> Sounds like a great feature!
>
Thanks!
Attached is a draft patch implementing the idea. To play with it, you
shall create the follow two foreign tables:
CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
create foreign table pg_planner_rels(rel text, content text)server
pglog options(filename '<your_install>/data/debug_planner_relopt.csv',
format 'csv');
create foreign table pg_planner_paths(rel text, path text, replacedby
text, reason int, startupcost float, totalcost float, cheapest text,
innerp text, outerp text, content text) server pglog options(filename
'<your_install>/data/debug_planner_paths.csv', format 'csv');
Example output attached.
Questions:
1. Which document shall we update? This is more than existing
debug_print_ knobs.
2. GEQO is not supported yet. I would suggest we do that with a
separate check in.
3. Where do we want to put the csv files? Currently I just put them under /data.
4. Do we want to push these two foreign tables into system_view.sql?
One problem is that foreign table needs a absolute path. Any way to
handle this?
5. As the output is csv file: I wrap strings with '"' but not sure
within the string itself if there any. Do we have any guarantee here?
Thanks,
Qingqing
---
postgres=# select p.rel, p.path, p.replacedby, p.reason,
p.startupcost, p.totalcost, p.cheapest, p.innerp, p.outerp,
substr(p.content, 1,30),r.content from pg_planner_paths p join
pg_planner_rels r on p.rel=r.rel;
rel | path | replacedby | reason | startupcost | totalcost
| cheapest | innerp | outerp | substr
| content
-----------+-----------+------------+--------+-------------+-----------+----------------------+-----------+-----------+--------------------------------+------------------------------------------------
0x2791a10 | 0x279d4b0 | | | 0 | 40.1
| +total+startup+param | | | ForeignScan(1)
rows=301 cost=0 | RELOPTINFO (1): rows=301 width=244
0x279f998 | 0x27a2238 | | | 0 | 1.1
| +total+startup+param | | | ForeignScan(1) rows=1
cost=0.0 | RELOPTINFO (1): rows=1 width=244
0x279fbd0 | 0x27a28b8 | | | 0 | 1.1
| +total+startup+param | | | ForeignScan(2) rows=1
cost=0.0 | RELOPTINFO (2): rows=1 width=64
0x27a2ab0 | 0x27a3c68 | | | 0 | 2.21
| +total+startup+param | 0x27a28b8 | 0x27a2238 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4608 | 0x27a4608 | 2 | 1.11 | 2.23
| | 0x27a2238 | 0x27a28b8 | HashJoin(1 2) rows=1
cost=1.11 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4498 | 0x27a4498 | 0 | 0 | 2.22
| | 0x27a4330 | 0x27a28b8 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4388 | 0x27a4388 | 0 | 0 | 2.21
| | 0x27a2238 | 0x27a28b8 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a4220 | 0x27a4220 | 2 | 2.22 | 2.25
| | 0x27a2238 | 0x27a28b8 | MergeJoin(1 2) rows=1
cost=2.2 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3f90 | 0x27a3f90 | 2 | 1.11 | 2.23
| | 0x27a28b8 | 0x27a2238 | HashJoin(1 2) rows=1
cost=1.11 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3e20 | 0x27a3e20 | 0 | 0 | 2.22
| | 0x27a3c10 | 0x27a2238 | NestLoop(1 2) rows=1
cost=0.00 | RELOPTINFO (1 2): rows=1 width=308
0x27a2ab0 | 0x27a3b18 | 0x27a3c68 | 1 | 2.22 | 2.25
| | 0x27a28b8 | 0x27a2238 | MergeJoin(1 2) rows=1
cost=2.2 | RELOPTINFO (1 2): rows=1 width=308
Attachment
Qingqing Zhou wrote: > Attached is a draft patch implementing the idea. To play with it, you > shall create the follow two foreign tables: > CREATE EXTENSION file_fdw; > CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw; > create foreign table pg_planner_rels(rel text, content text)server > pglog options(filename '<your_install>/data/debug_planner_relopt.csv', > format 'csv'); > create foreign table pg_planner_paths(rel text, path text, replacedby > text, reason int, startupcost float, totalcost float, cheapest text, > innerp text, outerp text, content text) server pglog options(filename > '<your_install>/data/debug_planner_paths.csv', format 'csv'); I think this is a pretty neat idea, but I'm not sure this user interface is a good one. Why not have a new option for EXPLAIN, so you would call "EXPLAIN (planner_stuff=on)" and it returns this as a resultset? This idea of creating random CSV files seems odd and inconvenient in the long run. For instance it fails if you have two sessions doing it simultaneously; you could tack the process ID at the end of the file name to prevent that problem, but then the foreign table breaks each time. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Jul 27, 2015 at 8:20 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > I think this is a pretty neat idea, but I'm not sure this user interface > is a good one. Why not have a new option for EXPLAIN, so you would call > "EXPLAIN (planner_stuff=on)" and it returns this as a resultset? Thank you for the feedback. Yeah, I agree piggy back on EXPLAIN sounds a better interface. A good thing about GUC is that it is global, so deep in planner we can see it. For example, in add_path(), where we add tracking of discarded paths. If we do EXPLAIN, we may either have to borrow another global variable, or add a flag on several planner data structures to make sure the flag can penetrate deep. Another thing is that with a GUC, we can mark it internal (PGC_INTERNAL), which compatibility maintenance might be relaxed, especially for this advanced option. > This idea of creating random CSV files seems odd and inconvenient in the > long run. For instance it fails if you have two sessions doing it > simultaneously; you could tack the process ID at the end of the file > name to prevent that problem, but then the foreign table breaks each > time. The reason to use CSV file is a kinda of balance. We do have other options, like pass data to pgstat, or persist in some shared memory/heap, but they all have their own issues. Any suggestion here? The file name is not random, it is fixed so we can create foreign table once and use it afterwards - I actually want to push them into system_views.sql. The file is opened with O_APPEND, the same way as log files, so concurrent writes are serialized. Read could be problematic though as no atomic guarantee between read/write. This is however a general issue of file_fdw, as the file is out of control of the core. We shall expect query returning format errors with concurrent read/write, and retry shall resolve the issue. Thanks, Qingqing
Qingqing Zhou wrote: > On Mon, Jul 27, 2015 at 8:20 PM, Alvaro Herrera > <alvherre@2ndquadrant.com> wrote: > > > > I think this is a pretty neat idea, but I'm not sure this user interface > > is a good one. Why not have a new option for EXPLAIN, so you would call > > "EXPLAIN (planner_stuff=on)" and it returns this as a resultset? > > Thank you for the feedback. > > Yeah, I agree piggy back on EXPLAIN sounds a better interface. A good > thing about GUC is that it is global, so deep in planner we can see it. Um, okay, I gather that GUC is convenient to use for this purpose. I don't see it as a good choice; I think a bare separate global variable at the C level is more appropriate. > > This idea of creating random CSV files seems odd and inconvenient in the > > long run. For instance it fails if you have two sessions doing it > > simultaneously; you could tack the process ID at the end of the file > > name to prevent that problem, but then the foreign table breaks each > > time. > > The reason to use CSV file is a kinda of balance. We do have other > options, like pass data to pgstat, or persist in some shared memory/heap, > but they all have their own issues. Any suggestion here? I would have a tuplestore, and the planner code would push tuples to it. After the planning is done, EXPLAIN can read and return tuples from the store to the user. > The file name is not random, it is fixed so we can create foreign table > once and use it afterwards - I actually want to push them into > system_views.sql. Got that. That seems fragile and not very convenient; I don't think forcing retries until no concurrent writers were using the same file is convenient at all. When you need this facility the most, which is during slow planner runs, it is more likely that somebody else will overwrite your file. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> Qingqing Zhou wrote:
>> The file name is not random, it is fixed so we can create foreign table
>> once and use it afterwards - I actually want to push them into
>> system_views.sql.
> Got that. That seems fragile and not very convenient; I don't think
> forcing retries until no concurrent writers were using the same file is
> convenient at all. When you need this facility the most, which is
> during slow planner runs, it is more likely that somebody else will
> overwrite your file.
FWIW, I would be very much against anything that requires going through
the filesystem for this. That will create security/privilege issues that
we should not want to introduce, quite aside from the usability problems
Alvaro points out.
regards, tom lane
On Tue, Jul 28, 2015 at 12:08 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > I would have a tuplestore, and the planner code would push tuples to it. > After the planning is done, EXPLAIN can read and return tuples from the > store to the user. > Not sure if I got it: so EXPLAIN will return tuples to libpq client. But how do we store these returned tuples (RelOptInfo, Path etc) so we can throw queries against them later? Something like this: INSERT INTO my_space SELECT (EXPLAIN SELECT ...); -- won't get parsed Regards, Qingqing
Qingqing Zhou <zhouqq.postgres@gmail.com> writes:
> Not sure if I got it: so EXPLAIN will return tuples to libpq client. But
> how do we store these returned tuples (RelOptInfo, Path etc) so we can
> throw queries against them later?
> Something like this:
> INSERT INTO my_space SELECT (EXPLAIN SELECT ...); -- won't get parsed
You can do something like that in plpgsql, for example
declare t text;
for t in EXPLAIN SELECT ... loop insert into whatever values(t); end loop;
There's an example of doing this sort of thing in the brin.sql regression
test, where it's used to verify that we're getting the plan type we
expect.
I don't feel a strong need to invent additional forms of that wheel.
regards, tom lane
On Tue, Jul 28, 2015 at 2:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> You can do something like that in plpgsql, for example
>
> declare t text;
>
> for t in EXPLAIN SELECT ...
> loop
> insert into whatever values(t);
> end loop;
>
I see - this is cool.
There are still something bothering me: EXPLAIN is a mixed output with
original text, rows for RelOptInfo, rows for Paths and possible others
added later. So we have to use 't as text' to receive each line. To do the
insertion, we have to further decompose each text line into fields, and
then do the insertion - seems quite involved with plpgsql programming. So
to simplify user's task, we may end up introduce some function to do this,
like this:
/* EXPLAIN target query and dump records to target tables */
select pg_dump_searchspace('target_table_for_rel', 'target_table_for_paths', 'select ... /* target query */');
Is this something we want?
Regards,
Qingqing
Qingqing Zhou <zhouqq.postgres@gmail.com> writes:
> There are still something bothering me: EXPLAIN is a mixed output with
> original text, rows for RelOptInfo, rows for Paths and possible others
> added later. So we have to use 't as text' to receive each line. To do the
> insertion, we have to further decompose each text line into fields, and
> then do the insertion - seems quite involved with plpgsql programming.
Well, that's only true if we don't expend some effort to make it better.
I could imagine, for instance, that if you specify the EXPLAIN option that
turns on this additional output, the output is no longer just a single
"text" column but is multiple columns. Perhaps one column could be a
key indicating what the other column(s) contain.
Another point is that we decided a long time ago that EXPLAIN's plain-text
output format is not intended to be machine-parsable, and so objecting to
a design on the grounds that it makes machine parsing harder is pretty
wrongheaded. I'd think there is plenty of room for dropping in additional
output data in the non-text output formats. That needs some careful
document-schema design effort, for sure, but it doesn't seem like it would
be particularly hard.
regards, tom lane
On Tue, Jul 28, 2015 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Another point is that we decided a long time ago that EXPLAIN's plain-text
> output format is not intended to be machine-parsable, and so objecting to
> a design on the grounds that it makes machine parsing harder is pretty
> wrongheaded. I'd think there is plenty of room for dropping in additional
> output data in the non-text output formats.
I think this will work, for example, I can put several sections of the
JSON output:
{ "plan": { // original EXPLAIN plan tree sits here }, "paths":{ // paths
consideredsits here } // ...
}
But still, it requires an extra step for user: he will needs to
programming to read through output (easier) and persists into a table for
later query.
Can we simplify above with foreign table methods? There are two major
concerns about this method per previous discussions: security and
usability. I think the main cause is the sharing foreign table design. How
about we put foreign table in separate pg_stat_tmp/<pid> folders, similar
to what Alvaro proposes, and similar to /proc file system. Meanwhile, we
introduce a function to help user create foreign table mapping to these
files. This looks solves the security and usability issues to me:
postgres=# select pg_debug_planner_init(); Foreign table 'pg_planner_rels', 'pg_planner_paths' created.
postgres=# EXPLAIN (debug_planner=on, ...) ... ... postgres=# select * from pg_planner_paths; ...
Thoughts?
Qngqing
Qingqing Zhou wrote: > Can we simplify above with foreign table methods? There are two major > concerns about this method per previous discussions: security and > usability. I think the main cause is the sharing foreign table design. I think foreign data wrappers are great. I do not think that we should try to shape every problem to look like foreign data so that we can solve it with a foreign data wrapper. I am a bit nervous that this keeps being brought up. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 7/29/15 2:40 PM, Alvaro Herrera wrote: > Qingqing Zhou wrote: > >> Can we simplify above with foreign table methods? There are two major >> concerns about this method per previous discussions: security and >> usability. I think the main cause is the sharing foreign table design. > > I think foreign data wrappers are great. I do not think that we should > try to shape every problem to look like foreign data so that we can > solve it with a foreign data wrapper. I am a bit nervous that this > keeps being brought up. Agreed. I think a better option would be shoving it into a backend tuplestore and just leaving it there (maybe with a command to clear it for the paranoid). That gives a relation you can query against, insert into another table, etc. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Data in Trouble? Get it in Treble! http://BlueTreble.com
On Thu, Jul 30, 2015 at 2:42 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > > I think a better option would be shoving it into a backend tuplestore and > just leaving it there (maybe with a command to clear it for the paranoid). > That gives a relation you can query against, insert into another table, etc. > -- This is something I don't know how to do it: in my understanding, a tuplestore is an internal store, which means it has no name exposed. Thus we can't reference it later. Regards, Qingqing
Qingqing Zhou wrote: > On Thu, Jul 30, 2015 at 2:42 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote: > > > > I think a better option would be shoving it into a backend tuplestore and > > just leaving it there (maybe with a command to clear it for the paranoid). > > That gives a relation you can query against, insert into another table, etc. > > This is something I don't know how to do it: in my understanding, a > tuplestore is an internal store, which means it has no name exposed. > Thus we can't reference it later. Yeah, that doesn't sound the kind of project you should attempt here. EXPLAIN already knows to return tuples, so I was assuming you would return your stuff using that mechanism. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services