Thread: Re: [BUGS] BUG #2429: Explain does not report object's schema
Moving to -hackers On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote: > > Don't know whether this would help in your situation, but you can make the > > output of EXPLAIN disambiguous by using table aliases: > > > > EXPLAIN > > SELECT * > > FROM schema1.mytable AS mt1, schema2.mytable AS mt2 > > WHERE 1=0 > > > > (The AS keyword is optional.) > > > > The aliases will be included in the query plan output. > > > Thanks for the notice, but to do this, I would have to raise errors on > queries having tables with the same name, which is not what I meant. > > But, if there is no other way, I'll do it. So you actually write out schemaname.tablename.fieldname for every field in the SELECT clause? Yikes! In any case, I agree that there should be a way to have EXPLAIN (and other things) show schema names. But since this isn't an itch that any of the developers have felt like scratching, it's unlikely this will happen unless someone sponsors it. :/ Unless folks think it's specifically a bad idea, can we at least get it on the TODO so if someone's bored it might get done? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
I don't think Cristiano is asking for the schema_name in the EXPLAIN o/p. The request is for the table ALIASes to be shown in the o/p, which makes more sense than schema_name+table_name, since the same table can be used in the same query more than once. Gurjeet. On 5/15/06, Jim C. Nasby <jnasby@pervasive.com> wrote: > Moving to -hackers > > On Wed, May 10, 2006 at 11:26:30AM -0300, Cristiano Duarte wrote: > > > Don't know whether this would help in your situation, but you can make the > > > output of EXPLAIN disambiguous by using table aliases: > > > > > > EXPLAIN > > > SELECT * > > > FROM schema1.mytable AS mt1, schema2.mytable AS mt2 > > > WHERE 1=0 > > > > > > (The AS keyword is optional.) > > > > > > The aliases will be included in the query plan output. > > > > > Thanks for the notice, but to do this, I would have to raise errors on > > queries having tables with the same name, which is not what I meant. > > > > But, if there is no other way, I'll do it. > > So you actually write out schemaname.tablename.fieldname for every field > in the SELECT clause? Yikes! > > In any case, I agree that there should be a way to have EXPLAIN (and > other things) show schema names. But since this isn't an itch that any > of the developers have felt like scratching, it's unlikely this will > happen unless someone sponsors it. :/ > > Unless folks think it's specifically a bad idea, can we at least get it > on the TODO so if someone's bored it might get done? > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:
> I don't think Cristiano is asking for the schema_name in the
> EXPLAIN o/p. The request is for the table ALIASes to be shown in the
> o/p, which makes more sense than schema_name+table_name, since the
> same table can be used in the same query more than once.
But EXPLAIN has always shown the aliases.
Possibly a reasonable compromise would be for EXPLAIN to act like rule
reverse-listing does, that is, consult the schema search path and print
a qualified name only if the table wouldn't be found without it.
regards, tom lane
On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote: > I don't think Cristiano is asking for the schema_name in the > EXPLAIN o/p. The request is for the table ALIASes to be shown in the > o/p, which makes more sense than schema_name+table_name, since the > same table can be used in the same query more than once. As has been pointed out, aliases ave always been displayed. The OPs problem was that he was using schema.tablename everywhere and explain didn't distinguish between schema1.mytable and schema2.mytable. It was suggested that he use aliases instead to make it work. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On 5/16/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > On Tue, May 16, 2006 at 09:15:13AM +0530, Gurjeet Singh wrote: > As has been pointed out, aliases ave always been displayed. The OPs > problem was that he was using schema.tablename everywhere and explain > didn't distinguish between schema1.mytable and schema2.mytable. It was > suggested that he use aliases instead to make it work. > I should subscribe to the -bugs mailing list too!!!! I didn't get the complete picture there. I don't think adding a schema_name to EXPLAIN's output, is really a good idea!! The ALIAS feature of the SQL language caters to this very need of assigning unambiguous names to tables. And we should be careful when adding any special code for EXPLAIN [ ANALYZE ]. For an example what would happen if we do that, consider this: If a big JOIN query takes N minutes on MS SQL Server, and I wish to see the plan why it is taking that long, one would expect me to enable 'Show Execution Plan' in the SQL Query Analyzer (similar to PG's EXPLAIN ANALYZE). And when I do that, the query now takes more than 2*N minutes to come back. I assume this extra delay is caused by the overhead of extra processing it does at row-source level (like how many rows passed through this row-source, average row-size, etc.).
On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote: > If a big JOIN query takes N minutes on MS SQL Server, and I wish > to see the plan why it is taking that long, one would expect me to > enable 'Show Execution Plan' in the SQL Query Analyzer (similar to > PG's EXPLAIN ANALYZE). And when I do that, the query now takes more > than 2*N minutes to come back. I assume this extra delay is caused by > the overhead of extra processing it does at row-source level (like how > many rows passed through this row-source, average row-size, etc.). I posted a patch last week on -patches which should dramatically cut the overhead of EXPLAIN ANALYZE. Perhaps you could try that and report your experience. http://archives.postgresql.org/pgsql-patches/2006-05/msg00158.php BTW, just showing the plan takes no time at all, just use EXPLAIN without the ANALYZE. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Gurjeet Singh wrote: > I don't think Cristiano is asking for the schema_name in the > EXPLAIN o/p. In fact I'm requesting for schema_name in EXPLAIN o/p. > The request is for the table ALIASes to be shown in the > o/p, which makes more sense than schema_name+table_name, since the > same table can be used in the same query more than once. SQL table aliases doesn't help locating the real place where the table is. If I have a table named "test" at the schema "place" and I do: "EXPLAIN SELECT * FROM place.test mytest" I will get: "Seq Scan on test mytest" With this output I know that "mytest" is an alias to "test", and that's great, much helpful than aliases only, but, where is "test"? Explain didn't provide me will the location: I know the table name but I don't know where it is. I don't see too much harm if the output was: "Seq Scan on place.test mytest" And that's what I'm asking for. Regards, Cristiano
On Tue, May 16, 2006 at 04:36:36PM +0530, Gurjeet Singh wrote: > I don't think adding a schema_name to EXPLAIN's output, is really > a good idea!! The ALIAS feature of the SQL language caters to this > very need of assigning unambiguous names to tables. That's assuming that the query was written with aliases. Otherwise you have to go back and manually add them, which can be a royal pain for a large, complex query. > And we should be careful when adding any special code for EXPLAIN > [ ANALYZE ]. For an example what would happen if we do that, consider > this: > > If a big JOIN query takes N minutes on MS SQL Server, and I wish > to see the plan why it is taking that long, one would expect me to > enable 'Show Execution Plan' in the SQL Query Analyzer (similar to > PG's EXPLAIN ANALYZE). And when I do that, the query now takes more > than 2*N minutes to come back. I assume this extra delay is caused by > the overhead of extra processing it does at row-source level (like how > many rows passed through this row-source, average row-size, etc.). How does that have anything to do with adding query names to EXPLAIN output?? The only argument against this that makes any sense to me is that EXPLAIN is plenty verbose as it is, and we don't need to be making it worse. But that's a non-issue if showing the schema names is optional. One way to address this would be to add a verbosity level to EXPLAIN. Right now, EXPLAIN VERBOSE is pretty useless to users, but there is additional information that would be handy to get from explain at different levels: "side effect" timing, such as time spent in triggers, constraints, etc. This is there in HEAD for triggers. Information about what other plans were considered. More explicit naming information. Information about statements that ran inside a function (ie: EXPLAIN SELECT function_name() is pretty useless today). Having a means to specify a verbosity level would allow for adding these kind of features without needlessly cluttering up a run-of-the-mill EXPLAIN. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
I agree... VERBOSE option can be made parameterised to include additional information in the EXPLAIN's output. I also agree that adding the schema name wouldn't add any overhead, and I support Tom's suggestion: 'Possibly a reasonable compromise would be for EXPLAIN to act like rule reverse-listing does,' But one should be wary of adding any other option that itself might cause an overhead, especially when doing the ANALYZE. For example, from the ones you suggested, 'showing other plans considered by the optimizer' seems a bit of an overhead. As the number of JOINed tables increase, so does the number of join permutations, and trying to keep the plans (in any form) till we send the results to client, would block-up considerable amount of resources. On the other hand, we can add these options and keep a note in docs saying that the presence of these particular parameters (to VERBOSE) will affect performance, and if used in conjunction with ANALYZE, ANALYZE might not give you the correct picture! On 5/16/06, Jim C. Nasby <jnasby@pervasive.com> wrote: > How does that have anything to do with adding query names to EXPLAIN > output?? > > The only argument against this that makes any sense to me is that > EXPLAIN is plenty verbose as it is, and we don't need to be making it > worse. But that's a non-issue if showing the schema names is optional. > > One way to address this would be to add a verbosity level to EXPLAIN. > Right now, EXPLAIN VERBOSE is pretty useless to users, but there is > additional information that would be handy to get from explain at > different levels: >
Cristiano Duarte wrote: > SQL table aliases doesn't help locating the real place where the table is. > If I have a table named "test" at the schema "place" and I do: > > "EXPLAIN SELECT * FROM place.test mytest" > > I will get: > > "Seq Scan on test mytest" > > With this output I know that "mytest" is an alias to "test", and that's > great, much helpful than aliases only, but, where is "test"? Since you created the mytest alias, you sure know where it's pointing to. In fact I'd argue that this should instead display Seq Scan on mytest > I don't see too much harm if the output was: > > "Seq Scan on place.test mytest" Not much harm there, but there will be plenty harm on other node types where the output is already too wide. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Cristiano Duarte wrote: > >> SQL table aliases doesn't help locating the real place where the table >> is. If I have a table named "test" at the schema "place" and I do: >> >> "EXPLAIN SELECT * FROM place.test mytest" >> >> I will get: >> >> "Seq Scan on test mytest" >> >> With this output I know that "mytest" is an alias to "test", and that's >> great, much helpful than aliases only, but, where is "test"? > > Since you created the mytest alias, you sure know where it's pointing > to. In fact I didn't create the alias, I've got the query already made from a user function call, and now I have to know where the table is located. Also, the user may pass a query without the schema name and even on this scenario, I need to know the schema name and the "real" table name. > > In fact I'd argue that this should instead display > Seq Scan on mytest I agree with you if EXPLAIN should only be executed interactivelly(psql, pgadmin3, etc). But, since you can execute EXPLAIN as a regular query to the database, you may be "explaining" an user supplied query, and doing so, there is no way to previously know what the aliases mean. > > >> I don't see too much harm if the output was: >> >> "Seq Scan on place.test mytest" > > Not much harm there, but there will be plenty harm on other node types > where the output is already too wide. Jim C. Nasby suggested a verbosity level to EXPLAIN, using "VERBOSE". It may solve this issue without harming other node types where the output is already too wide. Regards, Cristiano Duarte