Re: [PERFORM] Poor query plan chosen in 9.0.3 vs 8.3.7

2011-05-05 Thread Tom Lane
Brian Connolly writes: > Any help that you can provide would be greatly appreciated. I'd suggest trying to get rid of the weird little subselects, like this one: > ... SELECT * FROM assayresult."c69d129_particle_size_result_fields" > WHERE (((SELECT Container FROM exp.Data WHERE RowId = DataId

Re: [PERFORM] Poor query plan chosen in 9.0.3 vs 8.3.7

2011-05-05 Thread Bosco Rama
Hey Brian, Brian Connolly wrote: > (I had to send a follow up email due the length of email restrictions on the > mailing list.) A tip for when you have this problem in the future -- turn off html mail. It will reduce your email message length by 50% - 90%. HTH Bosco. -- Sent via pgsql-perfor

Re: [PERFORM] Poor query plan chosen in 9.0.3 vs 8.3.7

2011-05-05 Thread Brian Connolly
Here is the explain analyze output for when enable_material is 'off' and information on the postgresql version, settings and server configuration (I had to send a follow up email due the length of email restrictions on the mailing list.) (I apologize for the length of these email messages. And if

Re: [PERFORM] ask the database engine tuning on the server

2011-05-05 Thread Kevin Grittner
Didik Prasetyo wrote: > I had a problem with performance engine database, I use the server > with the following specifications > > 1. its storage configuration? >Storage SCSI 15K RAID 5 > 2. how his network? > 2 gigabit bonding. > 3. type / behavior of applications that connect

Re: [PERFORM] VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?

2011-05-05 Thread Robert Haas
On Sat, Apr 30, 2011 at 4:51 AM, Hsien-Wen Chu wrote: > since the block size is 8k for the default, and it consisted with many > tuple/line; as my understand, if any tuple/line is changed(maybe > update, insert, delete). the block will be marked as dirty block. and > then it will be flashed to dis

[PERFORM] Poor query plan chosen in 9.0.3 vs 8.3.7

2011-05-05 Thread Brian Connolly
We have a generated query from our web application which takes far longer to complete in 9.0.4, than in 8.3.7 (>60sec in 9.0.4 ~10sec in 8.3.7) The query plan generated in 9.0, includes a Materialize step which takes the bulk of the time for the query. If I disable materialize (by running set ena

Re: [PERFORM] Explicit joins

2011-05-05 Thread Kevin Grittner
Rishabh Kumar Jain wrote: > I am getting data from 10 tables in a view. > I don't know on which pair of tables I have to add explicit joins > to improve performance. There's usually some fairly natural order in terms of understanding the request. I find it's often good to try to state in word

Re: [PERFORM] amazon ec2

2011-05-05 Thread Dave Page
On Thu, May 5, 2011 at 1:02 AM, Josh Berkus wrote: > >> FWIW, EnterpriseDB's "InfiniCache" provides the same caching benefit. The >> way that works is when PG goes to evict a page from shared buffers that page >> gets compressed and stuffed into a memcache cluster. When PG determines that >> a

Re: [PERFORM] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance => Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226

2011-05-05 Thread Sethu Prasad
I did the hot standby configured earlier and at that time I started using(querying) the standby database. May be something missed on the archive command. On Wed, May 4, 2011 at 8:44 PM, Josh Berkus wrote: > > > While doing so I got the "*Query failed ERROR: catalog is missing 1 > > attribute(s