[HACKERS] Doubt in index subplan query
Hello,
I have a query plan for a certain query
Nested Loop (cost=1.00..38761761090.50 rows=3000608 width=8)
-> Seq Scan on lineitem (cost=1.00..100213649.15 rows=6001215
width=8)
-> Index Scan using oindex2 on myorders (cost=0.00..6442.27 rows=1 width=4)
Index Cond: ("outer".l_orderkey = myorders.o_orderkey)
Filter: (subplan)
SubPlan
-> Index Scan using cnation on customer (cost=0.00..12859.39
rows=5251 width=0)
Index Cond: (c_nationkey = 10)
How is the subplan handled by postgres at index level ? Is any sort of hashing
done ?
Thanks and regards,
Suresh
Re: [HACKERS] Backend Stats Enhancement Request
> That's not where the problem is. The people who will be left holding > the short end of the stick are the ones who can't raise their SHMMAX > setting past a couple of megabytes. > > It might be feasible to make pg_stat_activity's max string length > a postmaster-start-time configuration option. I am fine with a postmaster-start-time configuration option. It is not as flexible as I would like, but would serve the immediate need and keep me from having to patch every release of Postgres we install on boxes. The load on our production servers really prohibits any kind of processing of the log files locally. We have tried using several log shipping methods to process the logs on a machine with fewer running processes. These large queries are generated by a third party tool that we have very limited control over. Some of the queries captured are as large 16K. The queries are poorly written/generated. David Miller River Systems, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
Tom Lane wrote: Decibel! <[EMAIL PROTECTED]> writes: That's not where the problem is. The people who will be left holding the short end of the stick are the ones who can't raise their SHMMAX setting past a couple of megabytes. It might be feasible to make pg_stat_activity's max string length a postmaster-start-time configuration option. That would seem to me to be the most prudent course. As much as it is important to pay attention to those "who can't raise their SHMMAX setting past a couple of megabutes", that is certainly not the majority of participants in this community. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
Decibel! <[EMAIL PROTECTED]> writes: > Also, I don't necessarily buy that 32k * max_connections is too much > shared memory; even with max_connections of 1000 that's only 32M, > which is trivial for any box that's actually configured for 1000 > connections. That's not where the problem is. The people who will be left holding the short end of the stick are the ones who can't raise their SHMMAX setting past a couple of megabytes. It might be feasible to make pg_stat_activity's max string length a postmaster-start-time configuration option. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
On Jun 19, 2008, at 10:26 AM, Alvaro Herrera wrote: David Miller wrote: That is fine.. Maybe a dynamic configurable parameter that can be set/updated while the database is running. If it were a parameter, it could not be changed while the database is running. This issue lies in the fact that we have queries larger than 1K and we would like to be able to capture the entire query from Postgres Studio without having to process the log files.. Have you considered using CSV logs instead? Should be easier to process. Would it be hard to have a backend write it's complete command out to a file if the command lasts more than X number of seconds, and then allow other backends to read it from there? It is extremely annoying to not be able to get the full query contents. Also, I don't necessarily buy that 32k * max_connections is too much shared memory; even with max_connections of 1000 that's only 32M, which is trivial for any box that's actually configured for 1000 connections. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
[HACKERS] TRIGGER or RULE for SELECT
Currently, there is a limitation in PostgreSQL that any ON SELECT RULE must be an unconditional SELECT action that is INSTEAD. The reasoning is "This restriction was required to make rules safe enough to open them for ordinary users, and it restricts ON SELECT rules to act like views." I cannot understand the logic behind this, as RULES are created by DBA's. Besides, going by that logic, a user could create a RULE like CREATE RULE silly AS ON SELECT TO table_a DO INSTEAD SELECT * FROM table_b. Not to mention, an INSERT, UPDATE, or DELETE rule is potentially much more dangerous. eg: ON INSERT DO INSTEAD SELECT function_to_delete(); Is that safe? Furthermore, it would be more beneficial to allow DO ALSO, so that an audit function could be invoked. Likewise, the same applies to TRIGGERS. ON SELECT should also be an option there for the same reason. Is there some way that modifying the CREATE RULE for SELECT, and/or TRIGGER could be presented to the developers? Thanks in advance, Melvin Davidson.
Re: [HACKERS] Plan targetlists in EXPLAIN output
I have been working on a project (for GSOC) to retrieve planner/optimizer details. As part of the project, I need machine parsable output. So, I thought I would dust off a patch I found from last year that Germán Caamaño submitted. I didn't see any further activity there so I integrated it into 8.4 and added a DTD. The output below is generated by using the added flag 'XML' to the EXPLAIN command. The DTD probably wouldn't be needed for every output instance and may need its own flag. I am coming up to speed on the planner internals, but it seems like this first EXPLAIN XML concept may have some use. Are there any strong opinions about the XML hierarchy? Is it enough to simply wrap the text output from EXPLAIN with XML tags? -Tom Raney QUERY PLAN --- ]> (32 rows) Greg Smith wrote: On Thu, 17 Apr 2008, Tom Lane wrote: For debugging the planner work I'm about to do, I'm expecting it will be useful to be able to get EXPLAIN to print the targetlist of each plan node, not just the quals (conditions) as it's historically done. I've heard that some of the academic users of PostgreSQL were hoping to add features in this area in order to allow better using planner internals for educational purposes. It would be nice if that were available for such purposes without having to recompile. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tsearch is non-multibyte-aware in a few places
I wrote: > This is safe if and only if t_isspace is never true for multibyte > characters ... can anyone think of a counterexample? Non-breaking space is a counterexample, so I pg_mblen-ified those loops too. Fortunately this code only executes during dictionary cache load, so a few extra cycles aren't too critical. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] tsearch is non-multibyte-aware in a few places
I've identified the cause of bug #4253: /* Trim trailing space */ while (*pbuf && !t_isspace(pbuf)) pbuf++; *pbuf = '\0'; At least on Macs, t_isspace is capable of returning "true" when pointed at the second byte of a 2-byte UTF8 character. This explains the report that the letter "à" has a problem when some other ones don't. Of course pbuf needs to be incremented using pg_mblen not just ++. I looked around for other occurrences of the same problem and found a couple. I also found occurrences of the same pattern for skipping whitespace: while (*s && t_isspace(s)) s++; This is safe if and only if t_isspace is never true for multibyte characters ... can anyone think of a counterexample? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
David Miller wrote: > That is fine.. Maybe a dynamic configurable parameter that can be > set/updated while the database is running. If it were a parameter, it could not be changed while the database is running. > This issue lies in the fact that we have queries larger than 1K and we > would like to be able to capture the entire query from Postgres Studio > without having to process the log files.. Have you considered using CSV logs instead? Should be easier to process. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
David Miller <[EMAIL PROTECTED]> writes: > I would like to request a change to the PGBE_ACTIVITY_SIZE #define in the > pgstat.h file. This value determines the max length of the SQL query > contained in the PgBackendStatus structure. > By increasing the value of this #define to 32768 we are able to capture > queries larger than the 1024 default using just pgAdmin or Postgres Studio. I > would like to have this change considered for future releases. > Highly unlikely that we'd push it so high, because of the amount of > shared memory it would eat. > regards, tom lane That is fine.. Maybe a dynamic configurable parameter that can be set/updated while the database is running. This issue lies in the fact that we have queries larger than 1K and we would like to be able to capture the entire query from Postgres Studio without having to process the log files.. Thanks, David Miller -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Backend Stats Enhancement Request
David Miller <[EMAIL PROTECTED]> writes: > I would like to request a change to the PGBE_ACTIVITY_SIZE #define in the > pgstat.h file. This value determines the max length of the SQL query > contained in the PgBackendStatus structure. > By increasing the value of this #define to 32768 we are able to capture > queries larger than the 1024 default using just pgAdmin or Postgres Studio. I > would like to have this change considered for future releases. Highly unlikely that we'd push it so high, because of the amount of shared memory it would eat. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Backend Stats Enhancement Request
I would like to request a change to the PGBE_ACTIVITY_SIZE #define in the pgstat.h file. This value determines the max length of the SQL query contained in the PgBackendStatus structure. By increasing the value of this #define to 32768 we are able to capture queries larger than the 1024 default using just pgAdmin or Postgres Studio. I would like to have this change considered for future releases. Thanks, David Miller River Systems, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ANY/SOME/ALL with noncommutable operators
Am Donnerstag, 19. Juni 2008 schrieb Tom Lane: > Making the commutator operator where you need it *is* a general solution. True. Let me rephrase. The problem is that when dealing with operator names such as ~~ and &&, coming up with commutator operator names will make a mess of readability. > I think there's a syntactic-ambiguity reason why the spec is like that... OK, that might need to be analyzed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ANY/SOME/ALL with noncommutable operators
Peter Eisentraut <[EMAIL PROTECTED]> writes: > I can do > 'abc' LIKE ANY (ARRAY['a%','b%']) > but not > ANY (ARRAY['abc', 'def']) LIKE '%a' > This seems to be a failing in the SQL standard. You can work around this by > creating your own operators, but maybe there should be a general solution, as > there are a lot of noncommutable operators and this example doesn't seem all > that unuseful in practice. > Comments? Making the commutator operator where you need it *is* a general solution. I think there's a syntactic-ambiguity reason why the spec is like that... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ANY/SOME/ALL with noncommutable operators
On Thu, Jun 19, 2008 at 11:31:02AM +0200, Peter Eisentraut wrote: > I can do > > 'abc' LIKE ANY (ARRAY['a%','b%']) > > but not > > ANY (ARRAY['abc', 'def']) LIKE '%a' > > This seems to be a failing in the SQL standard. You can work around > this by creating your own operators, but maybe there should be a > general solution, as there are a lot of noncommutable operators and > this example doesn't seem all that unuseful in practice. > > Comments? It's been proposed several times before, at least once by Yours Truly, without objections. I seem to recall it's a SMOP. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] G8: SOS Cambio Climático
Hola, Acabo de firmar una petición pidiendo a los países del G8 que dejen de sabotear el progreso hacia un tratado sobre el cambio climático. Pensé que te interesaría esta campaña: http://www.avaaz.org/es/g8_climate_wakeup/98.php?cl_tf_sign=1 El futuro de nuestro planeta está en juego en la cubre anua del G8 en Japón. Japón, Estados Unidos y Canada proponen un tratado débil de mucho menos reducciones de gases que lo que los científicos dicen es necesario para enfrentar el cambio climático. La comunidad de ciudadanos Avaaz.org entregará una petición urgente al Primer Ministro Japonés el 18 de Junio, por favor firma hoy y pasa la petición. Juntos, podremos prevenir que este grupo de 8 países ricos arruine el proceso para todos los demás. http://www.avaaz.org/es/g8_climate_wakeup/98.php?cl_tf_sign=1 Gracias Estás recibiendo este mensaje porque un amigo te lo ha enviado utilizando el sitio Avaaz.org.
[HACKERS] ANY/SOME/ALL with noncommutable operators
I can do 'abc' LIKE ANY (ARRAY['a%','b%']) but not ANY (ARRAY['abc', 'def']) LIKE '%a' This seems to be a failing in the SQL standard. You can work around this by creating your own operators, but maybe there should be a general solution, as there are a lot of noncommutable operators and this example doesn't seem all that unuseful in practice. Comments? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres + Window manager
Josh, >> I stated details of proposal on page 2-13, 67-75 of pdf-document >> http://sql50.euro.ru/sql5.16.4.pdf , and i ask to implement it. >> All my proposals are public domain. JB> I'm confused. You're planning to develop this I can't make this alone. JB> or you're looking for someone else to? Yes, i'm looking for programmers, which agree to support this initiative. Dmitry Turin SQL5 (5.16.4) http://sql50.euro.ru/sql5.16.4.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
