[PERFORM] planner, *_collapse_limit

2012-07-26 Thread Marcus Engene
Hi, I've read a little bit about join_collapse_limit and from_collapse_limit and I see their reason to exist. A stupid question: in algorithms 101 you're usually told to make a chess program and then you usually do a width first min max tree. A low level opponent would interrupt this possibl

Re: [PERFORM] odd planner again, pg 9.0.8

2012-07-25 Thread Marcus Engene
On 7/25/12 6:39 PM, Tom Lane wrote: Marcus Engene writes: Lacking index hints I have a bit of a problem with a slow select. I don't think you need index hints. What you probably do need is to increase join_collapse_limit and/or from_collapse_limit to deal with this complex query as a

[PERFORM] odd planner again, pg 9.0.8

2012-07-25 Thread Marcus Engene
Hi, Lacking index hints I have a bit of a problem with a slow select. select pic.objectid as pic_objectid ,pu.objectid as pu_objectid ,ppcr.preproc_me as pul_preproc_me ,pp.price_amount as pp_price_amount ,pim.aut_item_container as pim_aut_item_container ,COALESCE((sele

Re: [PERFORM] WAL in RAM

2011-11-29 Thread Marcus Engene
On 10/29/11 10:11 , Scott Marlowe wrote: In over 10 years of using hardware RAID controllers with battery backup on many many machines, I have had exactly zero data loss due to a failed battery backup. Of course proper monitoring is important, to make sure the batteries aren't old and dead, but

Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!

2011-11-01 Thread Marcus Engene
Hi Hashim, After I upgraded from Postgres 8.3/8.4 to 9.0 I had all sorts of problems with queries with many joins. Queries that used to take 1ms suddenly take half a minute for no apparent reason. I have 72GB which I think makes the planner go bonkers and be too eager doing a seq scan. I tri

Re: [PERFORM] WAL in RAM

2011-10-29 Thread Marcus Engene
On 10/28/11 5:45 , Kevin Grittner wrote: Marcus Engene wrote: Every now and then I have write peaks which causes annoying delay on my website Does anyone here have any recommendations here? For our largest machines we put WAL on a RAID1 drive pair dedicated to that

[PERFORM] WAL in RAM

2011-10-28 Thread Marcus Engene
Hi list, Every now and then I have write peaks which causes annoying delay on my website. No particular reason it seems, just that laws of probability dictates that there will be peaks every now and then. Anyway, thinking of ways to make the peaks more bareable, I saw the new 9.1 feature to

Re: [PERFORM] bitmask index

2011-07-05 Thread Marcus Engene
On 6/22/11 11:42 , Greg Smith wrote: On 06/22/2011 05:27 PM, Marcus Engene wrote: I have some tables with bitmask integers. Set bits are the interesting ones. Usually they are sparse. If it's sparse, create a partial index that just includes rows where the bit is set:

[PERFORM] bitmask index

2011-06-22 Thread Marcus Engene
Hi list, I use Postgres 9.0.4. I have some tables with bitmask integers. Set bits are the interesting ones. Usually they are sparse. -- Many rows & columns CREATE TABLE a_table ( objectid INTEGER PRIMARY KEY NOT NULL ,misc_bits INTEGER DEFAULT 0 NOT NULL ..

Re: [PERFORM] wildcard makes seq scan on prod db but not in test

2011-05-09 Thread Marcus Engene
On 5/9/11 9:59 , Kevin Grittner wrote: You don't need to do that; you can specify an opclass for the index to tell it that you don't want to order by the normal collation, but rather in a way which will allow the index to be useful for pattern matching: http://www.postgresql.org/docs/9.0/intera

Re: [PERFORM] wildcard makes seq scan on prod db but not in test

2011-05-09 Thread Marcus Engene
On 5/9/11 8:57 , Kevin Grittner wrote: That could be a difference is collations. What do you get from the query on this page for each database?: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin There's indeed a different collation. Why is this affecting? Can i force a column

[PERFORM] wildcard makes seq scan on prod db but not in test

2011-05-09 Thread Marcus Engene
Dear list, I have a table with a few million rows and this index: CREATE INDEX bond_item_common_x7 ON bond_item_common ((lower(original_filename))); There are about 2M rows on bonddump and 4M rows on bond90. bonddump is on a 8MB RAM machine, bond90 is on a 72MB RAM machine. The table is anal