Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
I wrote: the right way to make this faster is to refactor things so that we don't generate useless equivalence classes in the first place, or at least don't keep them around in the planner's lists once we realize they're useless. After a bit of hacking, I propose the attached patch. I like

Re: [HACKERS] sorted writes for checkpoints

2010-10-29 Thread Heikki Linnakangas
On 29.10.2010 06:00, Jeff Janes wrote: One of the items on the Wiki ToDo list is sorted writes for checkpoints. The consensus seemed to be that this should be done by adding hook(s) into the main code, and then a contrib module to work with those hooks. Is there an existing contrib module that

Re: [HACKERS] sorted writes for checkpoints

2010-10-29 Thread Itagaki Takahiro
On Fri, Oct 29, 2010 at 3:23 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon's argument in the thread that the todo item points to (http://archives.postgresql.org/pgsql-patches/2008-07/msg00123.php) is basically that we don't know what the best algorithm is yet and

Re: [HACKERS] archives, attachments, etc

2010-10-29 Thread Matteo Beccati
Hi Gurjeet, On 09/10/2010 22:54, Gurjeet Singh wrote: On Sat, Oct 9, 2010 at 3:30 PM, Dimitri Fontaine dimi...@2ndquadrant.fr mailto:dimi...@2ndquadrant.fr wrote: I wish our super admins would have some time to resume the work on the new archives infrastructure, that was about ready

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Leonardo Francalanci
On the other hand, if I use a similar test case to my original one (i.e. the tables are much wider) then the query planning takes 1.42 seconds in 9.1 with this patch instead of about 4.7 seconds as we observed it using PostgreSQL 9.0.0. The beginning of the gprof output now looks like

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Leonardo Francalanci
but I don't get any gain from the patch... explain time is still around 250 ms. Tried with 9000 partitions, time is still 2 secs. Small correction: I tried with 3000 partitions (FOR i IN 0..3000 ...) and got 250ms with both versions, with 9000 partitions 2 secs (again no gain from the

Re: [HACKERS] sorted writes for checkpoints

2010-10-29 Thread Robert Haas
On Fri, Oct 29, 2010 at 2:58 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Fri, Oct 29, 2010 at 3:23 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon's argument in the thread that the todo item points to

Re: [HACKERS] sorted writes for checkpoints

2010-10-29 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Simon's argument in the thread that the todo item points to (http://archives.postgresql.org/pgsql-patches/2008-07/msg00123.php) is basically that we don't know what the best algorithm is yet and benchmarking is a lot of work, so

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Leonardo Francalanci m_li...@yahoo.it writes: I tried a simple test case: create table t (a integer, b text); DO $$DECLARE i int; BEGIN FOR i IN 0..9000 LOOP EXECUTE 'create table t' || i || ' ( CHECK (a ' || i*10 || ' and a = ' || (i+1)*10 || ' ) ) INHERITS

Re: [HACKERS] Tab completion for view triggers in psql

2010-10-29 Thread David Fetter
On Tue, Oct 26, 2010 at 11:55:07AM +0900, Itagaki Takahiro wrote: On Tue, Oct 26, 2010 at 11:34 AM, David Fetter da...@fetter.org wrote: Do we need to 'add' it? Possibly.  My understanding is that it couldn't really replace it. Ah, I see. I was wrong. We can have modification privileges

Re: [HACKERS] crash in plancache with subtransactions

2010-10-29 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mié oct 27 18:18:06 -0300 2010: I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: One simple idea is to keep a flag along with the executor state to indicate that the executor state is currently in use. Set it just before calling

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Leonardo Francalanci
This is going to be dominated by constraint exclusion checking. There's basically no fix for that except a more explicit representation of the partitioning rules. Damn, I knew that was going to be more complicated :) So in which case does this patch help? I guess in a multi-index

Re: [HACKERS] crash in plancache with subtransactions

2010-10-29 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of mié oct 27 18:18:06 -0300 2010: I spent quite a bit of time trying to deal with the memory-leakage problem without adding still more bookkeeping overhead. It wasn't looking good, and then I had a sudden

[HACKERS] [PATCH] Cleanup: Compare pointers to NULL instead of 0

2010-10-29 Thread Marti Raudsepp
Coccinelle found a few places in the code where pointer expressions were compared to 0. I have changed them to NULL instead. There was one line that I didn't dare to touch, which looks like a false positive. src/backend/regex/regc_lex.c:849: if (v-now - save == 0 || ((int) c 0 (int) c =

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Boszormenyi Zoltan z...@cybertec.at writes: On the other hand, if I use a similar test case to my original one (i.e. the tables are much wider) then the query planning takes 1.42 seconds in 9.1 with this patch instead of about 4.7 seconds as we observed it using PostgreSQL 9.0.0. The beginning

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
I wrote: This is going to be dominated by constraint exclusion checking. Hmm, maybe I spoke too soon. With 9000 child tables I get a profile like this: samples %symbol name 447433 47.1553 get_tabstat_entry 185458 19.5456 find_all_inheritors 53064 5.5925 SearchCatCache 33864

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Robert Haas
On Fri, Oct 29, 2010 at 12:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Boszormenyi Zoltan z...@cybertec.at writes: On the other hand, if I use a similar test case to my original one (i.e. the tables are much wider) then the query planning takes 1.42 seconds in 9.1 with this patch instead of

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Leonardo Francalanci
Hmm, maybe I spoke too soon. With 9000 child tables I get a profile like this: Well, the 9000-table-test-case was meant to check the difference in performance with/without the patch... I don't see the reason for trying to optimize such an unrealistic case. BTW can someone explain to me

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Leonardo Francalanci m_li...@yahoo.it writes: BTW can someone explain to me which are the cases where the patch actually helps? Cases with lots of irrelevant indexes. Zoltan's example had 4 indexes per child table, only one of which was relevant to the query. In your test case there are no

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Oct 29, 2010 at 12:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: However, if the hot spot does stay in SearchCatCache, I can't help noticing that those bucket chains are looking a bit overloaded --- sixty-plus entries per bucket ain't good.  Maybe

Re: [HACKERS] Madam I

2010-10-29 Thread murphy pope
f Hi,Dear: we are wholesalers in china.We can sent our product to USA,Germany,Europe and so on. we have established a good business relationship with the manufactoies.So we can get the best price there. And we mainly do our business in bulk with our agents, so we should

[HACKERS] Fix for cube picksplit function

2010-10-29 Thread Alexander Korotkov
Hackers, Gist picksplit method implementation in cube contrib module contains a bug in Guttman's split algorithm implementation. It was mentioned before but it is still not fixed yet. Current implementation doesn't cause incorrect behavior, but index becomes very bad, because each index scan

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Alvaro Herrera
Excerpts from Tom Lane's message of vie oct 29 14:15:55 -0300 2010: I wrote: This is going to be dominated by constraint exclusion checking. Hmm, maybe I spoke too soon. With 9000 child tables I get a profile like this: samples %symbol name 447433 47.1553 get_tabstat_entry

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of vie oct 29 14:15:55 -0300 2010: samples %symbol name 447433 47.1553 get_tabstat_entry Is there a reason for keeping the pgstat info in plain lists? Yeah: anything else loses for small numbers of

[HACKERS] Tasks for Google Code-In

2010-10-29 Thread Selena Deckelmann
The Oregon State University's Open Source Lab (OSL) has contacted me about coordinating some student work for the Google Code-In, a program targetting 13-18 year olds. See more here: http://code.google.com/opensource/gci/2010-11/index.html This program specifically allows documentation, testing

Re: [HACKERS] [PATCH] Cleanup: Compare pointers to NULL instead of 0

2010-10-29 Thread Tom Lane
Marti Raudsepp ma...@juffo.org writes: Coccinelle found a few places in the code where pointer expressions were compared to 0. I have changed them to NULL instead. Applied, thanks! There was one line that I didn't dare to touch, which looks like a false positive.

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Leonardo Francalanci
Cases with lots of irrelevant indexes. Zoltan's example had 4 indexes per child table, only one of which was relevant to the query. In your test case there are no irrelevant indexes, which is why the runtime didn't change. Mmh... I must be doing something wrong. It looks to me it's not

Re: [HACKERS] [PATCH] Cleanup: Compare pointers to NULL instead of 0

2010-10-29 Thread Alvaro Herrera
Excerpts from Marti Raudsepp's message of vie oct 29 13:25:03 -0300 2010: Coccinelle found a few places in the code where pointer expressions were compared to 0. I have changed them to NULL instead. There was one line that I didn't dare to touch, which looks like a false positive.

Re: [HACKERS] plan time of MASSIVE partitioning ...

2010-10-29 Thread Tom Lane
Leonardo Francalanci m_li...@yahoo.it writes: Cases with lots of irrelevant indexes. Zoltan's example had 4 indexes per child table, only one of which was relevant to the query. In your test case there are no irrelevant indexes, which is why the runtime didn't change. Mmh... I must be

Re: [HACKERS] crash in plancache with subtransactions

2010-10-29 Thread Alvaro Herrera
Excerpts from Tom Lane's message of vie oct 29 12:54:52 -0300 2010: Alvaro Herrera alvhe...@commandprompt.com writes: I tried the original test cases that were handed to me (quite different from what I submitted here) and they are fixed also. Thanks. It'd be interesting to know if

Re: [HACKERS] sorted writes for checkpoints

2010-10-29 Thread Greg Smith
Itagaki Takahiro wrote: When I submitted the patch, I tested it on disk-based RAID-5 machine: http://archives.postgresql.org/pgsql-hackers/2007-06/msg00541.php But there were no additional benchmarking reports at that time. We still need benchmarking before we re-examine the feature. For

[HACKERS] [PATCH] More Coccinelli cleanups

2010-10-29 Thread Marti Raudsepp
Hi list, Since my previous Coccinelle cleanup patches have gained positive feedback I decided to try some more. This time I wrote my own spatches. patch 0001 turns (a - b == 0) into (a == b) and similarly with != patch 0002 applies the same to operators , =, , = I'm well aware that there's a