Thread: Constraint Exclusion on all tables
So far, the CE patch covers only inherited child tables and is only effective when enable_constraint_exclusion is true. There have been various requests for this to work with UNION ALL views and also with normal queries. Since we have a GUC that can turn this behaviour off, and is off by default, I think it is probably acceptable to have CE apply to ALL table accesses, not just inherited ones. That is considerably neater in implementation than trying to kludge it for UNION ALL cases. [In the future when we have plan invalidation: I would suggest that we keep enable_constraint_exclusion as a GUC. When set to true, this would apply for all tables. Inherited tables would always be considered for exclusion, whatever the setting of the GUC.] An idea for discussion is to hide the exclusion within set_plain_rel_pathlist, so that CE applies to all tables. If a table is excluded, we generate only the single "exclusion plan". I think we should introduce a new Node type of "No Scan", making it very clear in any Explain that we have excluded a table. The alternative is a Result node with an SeqScan below it... but the Result doesn't explain *why* it exists at that point. I've already used that form of coding and it works well enough. Inheritance queries would continue to act as they do now, where an excluded table is *not* shown; this is to allow for sensible size EXPLAINs when we have 100s of child tables. Comments? Best Regards, Simon Riggs
On Tue, Jul 19, 2005 at 10:23:49PM +0100, Simon Riggs wrote: > Inheritance queries would continue to act as they do now, where an > excluded table is *not* shown; this is to allow for sensible size > EXPLAINs when we have 100s of child tables. Since it's also possible to do partitioning with UNION ALL, maybe it would be better if there was an option to explain that told it either to show or not show info about eliminated partitions. That would seem to serve the general case better than coding it according to table type. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Tue, 2005-07-19 at 17:50 -0500, Jim C. Nasby wrote: > On Tue, Jul 19, 2005 at 10:23:49PM +0100, Simon Riggs wrote: > > Inheritance queries would continue to act as they do now, where an > > excluded table is *not* shown; this is to allow for sensible size > > EXPLAINs when we have 100s of child tables. > > Since it's also possible to do partitioning with UNION ALL, maybe it > would be better if there was an option to explain that told it either to > show or not show info about eliminated partitions. That would seem to > serve the general case better than coding it according to table type. Can you think up the syntax, so we can comment on that proposal? Best Regards, Simon Riggs
On 7/20/05, Simon Riggs <simon@2ndquadrant.com> wrote: > > Since it's also possible to do partitioning with UNION ALL, maybe it > > would be better if there was an option to explain that told it either to > > show or not show info about eliminated partitions. That would seem to > > serve the general case better than coding it according to table type. > Can you think up the syntax, so we can comment on that proposal? hmm, maybe something like: EXPLAIN [ ANALYZE ] [ VERBOSE ] [ WITH EXCLUDED ] Where WITH EXCLUDED would mean to show tables eliminated? Also, it would be a good time to suggest some way for making EXPLAIN script-friendly. Like return data as a computer program convenient table? Regards, Dawid
On Wed, Jul 20, 2005 at 12:52:28PM +0200, Dawid Kuroczko wrote: > On 7/20/05, Simon Riggs <simon@2ndquadrant.com> wrote: > > > Since it's also possible to do partitioning with UNION ALL, maybe it > > > would be better if there was an option to explain that told it either to > > > show or not show info about eliminated partitions. That would seem to > > > serve the general case better than coding it according to table type. > > Can you think up the syntax, so we can comment on that proposal? > > hmm, maybe something like: > EXPLAIN [ ANALYZE ] [ VERBOSE ] [ WITH EXCLUDED ] > > Where WITH EXCLUDED would mean to show tables eliminated? One thought I had about that... it might make sense to have different defaults for EXCLUDED depending on the table you're hitting. For an inherited table or a UNION ALL you would probably expect some elimination to happen, but for queries against other tables you would expect it not to happen (though I'm not certain you could even get exclusion outside of inherited tables or a union...) Also, I think it's possible that down the road people might want to just be shown excluded tables by default depending on some setting (probably a psql variable I imagine). Given those two possibilities, I think it would be better to do EXPLAIN [ ANALYZE ] [ VERBOSE ] [ ( WITH | WITHOUT ) EXCLUDED ] That way, the without syntax will be valid from the start. > Also, it would be a good time to suggest some way for making > EXPLAIN script-friendly. Like return data as a computer program > convenient table? I would absolutely love to see this happen. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Simon, > > Also, it would be a good time to suggest some way for making > > EXPLAIN script-friendly. Like return data as a computer program > > convenient table? I'm already trying to kludge this in Perl. It would help automated testing considerably if explain analyze could be output as tab-delimited text instead of the human-readable version. -- Josh Berkus Aglio Database Solutions San Francisco
On 7/21/05, Josh Berkus <josh@agliodbs.com> wrote: > > > Also, it would be a good time to suggest some way for making > > > EXPLAIN script-friendly. Like return data as a computer program > > > convenient table? > I'm already trying to kludge this in Perl. It would help automated testing > considerably if explain analyze could be output as tab-delimited text instead > of the human-readable version. Hmm, methinks it wouldn't be a very difficult "beginner's project" in PostgreSQL hacking, to add "script-friendly" format for EXPLAIN command. I am not sure if I'll make it, but I'm willing to try... If I'm jumping on a too big a beast, please tell me. :) Regards, Dawid
Dawid Kuroczko wrote: >Hmm, methinks it wouldn't be a very difficult "beginner's project" in >PostgreSQL hacking, to add "script-friendly" format for EXPLAIN >command. I am not sure if I'll make it, but I'm willing to try... It occured to me that it may be good to create a new datatype for the results. The data type's default textual format could be the same as the current output, but you could then provide functions that operate on these structures to extract the relavent bits in a format that your extrnal analysis tool can use. This would require that you can do SELECT * FROM (EXPLAIN SELECT 1); though -- I guess that may be quite a big job! >If I'm jumping on a too big a beast, please tell me. :) Me thinks I may have just made it a bit more difficult, but probably (should that be hopefully?) more generally useful. Sam
Le vendredi 22 juillet 2005 à 15:44 +0100, Sam Mason a écrit : > Dawid Kuroczko wrote: > >Hmm, methinks it wouldn't be a very difficult "beginner's project" in > >PostgreSQL hacking, to add "script-friendly" format for EXPLAIN > >command. I am not sure if I'll make it, but I'm willing to try... > > It occured to me that it may be good to create a new datatype for > the results. The data type's default textual format could be the > same as the current output, but you could then provide functions > that operate on these structures to extract the relavent bits in a > format that your extrnal analysis tool can use. Denys (in CC) wrote an XML output for EXPLAIN to be used by any program in a easy way. In that way, he replaced the current output of explain. So, I'm working in a patch that extends EXPLAIN and give the chance to get the output as text (as usual) or xml. So, you'd be able to run EXPLAIN ANALYZE XML to get the output in XML, that could be parsed by whatever script/program. I hope to get a patch in the next days, according to my spare time. Regards, -- Germán Poó Caamaño http://www.ubiobio.cl/~gpoo/
It seems current CE implementation ignores UPDATE, DELETE quries. Is this an intended limitation? -- Tatsuo Ishii > So far, the CE patch covers only inherited child tables and is only > effective when enable_constraint_exclusion is true. > > There have been various requests for this to work with UNION ALL views > and also with normal queries. > > Since we have a GUC that can turn this behaviour off, and is off by > default, I think it is probably acceptable to have CE apply to ALL table > accesses, not just inherited ones. That is considerably neater in > implementation than trying to kludge it for UNION ALL cases. > > [In the future when we have plan invalidation: I would suggest that we > keep enable_constraint_exclusion as a GUC. When set to true, this would > apply for all tables. Inherited tables would always be considered for > exclusion, whatever the setting of the GUC.] > > An idea for discussion is to hide the exclusion within > set_plain_rel_pathlist, so that CE applies to all tables. If a table is > excluded, we generate only the single "exclusion plan". I think we > should introduce a new Node type of "No Scan", making it very clear in > any Explain that we have excluded a table. The alternative is a Result > node with an SeqScan below it... but the Result doesn't explain *why* it > exists at that point. I've already used that form of coding and it works > well enough. > > Inheritance queries would continue to act as they do now, where an > excluded table is *not* shown; this is to allow for sensible size > EXPLAINs when we have 100s of child tables. > > Comments? > > Best Regards, Simon Riggs > > > ---------------------------(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 >
On Sun, 2005-07-24 at 17:57 +0900, Tatsuo Ishii wrote: > It seems current CE implementation ignores UPDATE, DELETE quries. Is > this an intended limitation? Yes, it does not currently optimise the execution of UPDATE/DELETE against a parent table. This is not an intended long-term limitation and I hope to fix this also. The code for this is actually in a different place to the code for SELECT, so I need to do extra work to fix that. My priority for CE was to provide for the most common rolling window use cases on very large databases, so in those cases UPDATEs or DELETEs against large tables are actually fairly suicidal statements; that meant that feature had a lower implementation priority....but as I say, I will get to that. Best Regards, Simon Riggs
Simon Riggs wrote: > On Sun, 2005-07-24 at 17:57 +0900, Tatsuo Ishii wrote: > > It seems current CE implementation ignores UPDATE, DELETE quries. Is > > this an intended limitation? > > Yes, it does not currently optimise the execution of UPDATE/DELETE > against a parent table. > > This is not an intended long-term limitation and I hope to fix this > also. The code for this is actually in a different place to the code for > SELECT, so I need to do extra work to fix that. My priority for CE was > to provide for the most common rolling window use cases on very large > databases, so in those cases UPDATEs or DELETEs against large tables are > actually fairly suicidal statements; that meant that feature had a lower > implementation priority....but as I say, I will get to that. Here are some new TODO items added based on this discusssion: * Allow EXPLAIN to identify tables that were skipped because of enable_constraint_exclusion * Allow EXPLAIN output to be more easily processed by scripts * Allow enable_constraint_exclusion to work for UNIONs like it does for inheritance * Allow enable_constraint_exclusion to work for UPDATE and DELETE queries -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073