[PERFORM] Using Gprof with Postgresql
Hi All, I compile PostgreSQL-8.4.0 with icc and --enable profiling option. I ran command psql and create table and make a select then I quit psql and go to .../data/gprof folder there are some folders named with numbers (I think they are query ids); all of them are empty. How can I solve this issue? Reydan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using Gprof with Postgresql
postgresql was faster than the files ;) (sorry, I just couldn't resist). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Using Gprof with Postgresql
On Mon, Sep 7, 2009 at 8:11 AM, Reydan Cankur wrote: > Hi All, > > I compile PostgreSQL-8.4.0 with icc and --enable profiling option. I ran > command psql and create table and make a select then I quit psql and go to > .../data/gprof folder there are some folders named with numbers (I think > they are query ids); They are the process ids (PIDs) of the backend processes. > all of them are empty. How can I solve this issue? > Does your compiler work for profiling in general? Can you compile other simpler programs for profiling with icc and have it work for them? If so, how? I thought gprof was specific to GNU compilers. Jeff
Re: [PERFORM] Using Gprof with Postgresql
Reydan Cankur writes: > I compile PostgreSQL-8.4.0 with icc and --enable profiling option. I > ran command psql and create table and make a select then I quit psql > and go to .../data/gprof folder there are some folders named with > numbers (I think they are query ids); all of them are empty. How can I > solve this issue? Well, you could use gcc ... icc claims to support the -pg switch but the above sounds like it just ignores it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
> -Mensaje original- > De: Karl Denninger > Enviado el: Sábado, 05 de Septiembre de 2009 21:19 > Para: Alvaro Herrera > CC: Tom Lane; Merlin Moncure; Josh Berkus; > pgsql-performance@postgresql.org > Asunto: Re: [PERFORM] Planner question - "bit" data types > > There was a previous thread and I referenced it. I don't have > the other one in my email system any more to follow up to it. > > I give up; the attack-dog crowd has successfully driven me off. Ciao. > > Alvaro Herrera wrote: > > Karl Denninger escribió: > > > Tom Lane wrote: > > > > > > You never showed us any EXPLAIN results, > > > Yes I did. Go back and look at the archives. > I provided full EXPLAIN > and EXPLAIN ANALYZE results for the original > query. Sheesh. > > > > You did? Where? This is your first message in this thread: > > http://archives.postgresql.org/pgsql-performance/2009-09/msg00059.php > No EXPLAINs anywhere to be seen. > I guess this is the post Karl refers to: http://archives.postgresql.org/pgsql-sql/2009-08/msg00088.php Still you can't hope that others will recall a post 2 weeks ago, with an other subject and in an other list! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > There was a previous thread and I referenced it. I don't have the other one > in my email system any more to follow up to it. > > I give up; the attack-dog crowd has successfully driven me off. Ciao. Perhaps I'm biased by knowing some of the people involved, but I don't think anyone on this thread has been anything but polite. It would certainly be great if PostgreSQL could properly estimate the selectivity of expressions like this without resorting to nasty hacks, but it can't, and unfortunately, there's really no possibility of that changing any time soon. Even if someone implements a fix today, the soonest it will appear in a production release is June 2010. So, any suggestion for improvement is going to be in the form of suggesting that you modify the schema in some way. I know that's not really what you're looking for, but unfortunately it's the best we can do. As far as I can tell, it is not correct to say that you referenced the previous thread. I do not see any such reference. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
Robert Haas wrote: > On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > >> There was a previous thread and I referenced it. I don't have the other one >> in my email system any more to follow up to it. >> >> I give up; the attack-dog crowd has successfully driven me off. Ciao. >> > > Perhaps I'm biased by knowing some of the people involved, but I don't > think anyone on this thread has been anything but polite. It would > certainly be great if PostgreSQL could properly estimate the > selectivity of expressions like this without resorting to nasty hacks, > but it can't, and unfortunately, there's really no possibility of that > changing any time soon. Even if someone implements a fix today, the > soonest it will appear in a production release is June 2010. So, any > suggestion for improvement is going to be in the form of suggesting > that you modify the schema in some way. I know that's not really what > you're looking for, but unfortunately it's the best we can do. > > As far as I can tell, it is not correct to say that you referenced the > previous thread. I do not see any such reference. > > ...Robert > > I was asking about modifying the schema. The current schema is an integer being used as a bitmask. If the planner knows how to handle a type of "bit(X)" (and will at least FILTER rather than NESTED LOOP it on a select, as happens for an Integer used in this fashion), that change is easier than splitting it into individual boolean fields. -- Karl <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
On Mon, Sep 7, 2009 at 8:51 PM, Karl Denninger wrote: > Robert Haas wrote: > > On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > > > There was a previous thread and I referenced it. I don't have the other one > in my email system any more to follow up to it. > > I give up; the attack-dog crowd has successfully driven me off. Ciao. > > > Perhaps I'm biased by knowing some of the people involved, but I don't > think anyone on this thread has been anything but polite. It would > certainly be great if PostgreSQL could properly estimate the > selectivity of expressions like this without resorting to nasty hacks, > but it can't, and unfortunately, there's really no possibility of that > changing any time soon. Even if someone implements a fix today, the > soonest it will appear in a production release is June 2010. So, any > suggestion for improvement is going to be in the form of suggesting > that you modify the schema in some way. I know that's not really what > you're looking for, but unfortunately it's the best we can do. > > As far as I can tell, it is not correct to say that you referenced the > previous thread. I do not see any such reference. > > ...Robert > > > > I was asking about modifying the schema. > > The current schema is an integer being used as a bitmask. If the planner > knows how to handle a type of "bit(X)" (and will at least FILTER rather than > NESTED LOOP it on a select, as happens for an Integer used in this fashion), > that change is easier than splitting it into individual boolean fields. Well, the first several replies seem to address that question - I think we all agree that won't help. I'm not sure what you mean by "at least FILTER rather than NESTED LOOP it on a select". However, typically, the time when you get a nested loop is when the planner believes that the loop will be executed very few times (in other words, the outer side will return very few rows). It probably isn't the case that the planner COULDN'T choose to execute the query in some other way; rather, the planner believes that the nested loop is faster because of a (mistaken) belief about how many rows the bitmap-criterion will actually match. All the suggestions you've gotten upthread are tricks to enable the planner to make a better estimate, which will hopefully cause it to choose a better plan. As a general statement, selectivity estimation problems are very painful to work around and often involve substantial application redesign. In all honesty, I think you've run across one of the easier variants. As painful as it is to hear the word easy applied to a problem that's killing you, there actually IS a good solution to this problem: use individual boolean fields. I know that's not what you want to do, but it's better than "sorry, you're hosed, no matter how you do this it ain't gonna work". And I do think there are a few in the archives that fall into that category. Good luck, and sorry for the bad news. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
"pgsql-performance-ow...@postgresql.org" wrote: On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: > There was a previous thread and I referenced it. I don't have the other one > in my email system any more to follow up to it. > > I give up; the attack-dog crowd has successfully driven me off. Ciao. Perhaps I'm biased by knowing some of the people involved, but I don't think anyone on this thread has been anything but polite. I use several online forums and this -- hands down -- is the best: not only for politeness even when the information I provided was misleading or the question I asked was, in retrospect, Duh? but also for 1) speed of response, 2) breadth of ideas and 3) accuracy of information -- often on complex issues with no simple solution from folk who probably have more to do than sit around waiting for the next post. My thanks to the knowledgeable people on this forum. Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
Robert Haas wrote: > On Mon, Sep 7, 2009 at 8:51 PM, Karl Denninger wrote: > >> Robert Haas wrote: >> >> On Sat, Sep 5, 2009 at 8:19 PM, Karl Denninger wrote: >> >> >> There was a previous thread and I referenced it. I don't have the other one >> in my email system any more to follow up to it. >> >> I give up; the attack-dog crowd has successfully driven me off. Ciao. >> >> >> Perhaps I'm biased by knowing some of the people involved, but I don't >> think anyone on this thread has been anything but polite. It would >> certainly be great if PostgreSQL could properly estimate the >> selectivity of expressions like this without resorting to nasty hacks, >> but it can't, and unfortunately, there's really no possibility of that >> changing any time soon. Even if someone implements a fix today, the >> soonest it will appear in a production release is June 2010. So, any >> suggestion for improvement is going to be in the form of suggesting >> that you modify the schema in some way. I know that's not really what >> you're looking for, but unfortunately it's the best we can do. >> >> As far as I can tell, it is not correct to say that you referenced the >> previous thread. I do not see any such reference. >> >> ...Robert >> >> >> >> I was asking about modifying the schema. >> >> The current schema is an integer being used as a bitmask. If the planner >> knows how to handle a type of "bit(X)" (and will at least FILTER rather than >> NESTED LOOP it on a select, as happens for an Integer used in this fashion), >> that change is easier than splitting it into individual boolean fields. >> > > Well, the first several replies seem to address that question - I > think we all agree that won't help. I'm not sure what you mean by "at > least FILTER rather than NESTED LOOP it on a select". However, > typically, the time when you get a nested loop is when the planner > believes that the loop will be executed very few times (in other > words, the outer side will return very few rows). It probably isn't > the case that the planner COULDN'T choose to execute the query in some > other way; rather, the planner believes that the nested loop is faster > because of a (mistaken) belief about how many rows the > bitmap-criterion will actually match. All the suggestions you've > gotten upthread are tricks to enable the planner to make a better > estimate, which will hopefully cause it to choose a better plan. > > As a general statement, selectivity estimation problems are very > painful to work around and often involve substantial application > redesign. In all honesty, I think you've run across one of the easier > variants. As painful as it is to hear the word easy applied to a > problem that's killing you, there actually IS a good solution to this > problem: use individual boolean fields. I know that's not what you > want to do, but it's better than "sorry, you're hosed, no matter how > you do this it ain't gonna work". And I do think there are a few in > the archives that fall into that category. > > Good luck, and sorry for the bad news. > > ...Robert > The individual boolean fields don't kill me and in terms of some of the application issues they're actually rather easy to code for. The problem with re-coding for them is extensibility (by those who install and administer the package); a mask leaves open lots of extra bits for "site-specific" use, where hard-coding booleans does not, and since the executable is a binary it instantly becomes a huge problem for everyone but me. It does appear, however, that a bitfield doesn't evaluate any differently than does an integer used with a mask, so there you have it. it is what it is, and if I want this sort of selectivity in the search I have no choice. -- Karl <> -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
Karl Denninger escribió: > The individual boolean fields don't kill me and in terms of some of the > application issues they're actually rather easy to code for. > > The problem with re-coding for them is extensibility (by those who > install and administer the package); a mask leaves open lots of extra > bits for "site-specific" use, where hard-coding booleans does not, and > since the executable is a binary it instantly becomes a huge problem for > everyone but me. Did you try hiding the bitmask operations inside a function as Tom suggested? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
On Mon, Sep 7, 2009 at 10:05 PM, Karl Denninger wrote: > The individual boolean fields don't kill me and in terms of some of the > application issues they're actually rather easy to code for. > > The problem with re-coding for them is extensibility (by those who install > and administer the package); a mask leaves open lots of extra bits for > "site-specific" use, where hard-coding booleans does not, and since the > executable is a binary it instantly becomes a huge problem for everyone but > me. > > It does appear, however, that a bitfield doesn't evaluate any differently > than does an integer used with a mask, so there you have it. it is what > it is, and if I want this sort of selectivity in the search I have no > choice. You can always create 32 boolean fields and only use some of them, leaving the others for site-specific use... ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Planner question - "bit" data types
Robert Haas writes: > On Mon, Sep 7, 2009 at 10:05 PM, Karl Denninger wrote: >> The problem with re-coding for them is extensibility (by those who install >> and administer the package); a mask leaves open lots of extra bits for >> "site-specific" use, where hard-coding booleans does not, > You can always create 32 boolean fields and only use some of them, > leaving the others for site-specific use... Indeed. Why is "user_defined_flag_24" so much worse that "mask & 16777216" ? Especially when the day comes that you need to add one more system-defined flag bit? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
