[PERFORM] Improve the Postgres Query performance

2011-06-22 Thread Tripura
Hello All.. This is my first PostgreSql database. It has 8 tables and 4 tables are very huge each with 6million records. I have a simple view on this tables and it is taking more than 3hrs to return the results. Can someone help me the way to improve the db return the results in a faster way. I

Re: [PERFORM] Improve the Postgres Query performance

2011-06-22 Thread Craig Ringer
On 22/06/11 04:34, Tripura wrote: Hello All.. This is my first PostgreSql database. It has 8 tables and 4 tables are very huge each with 6million records. I have a simple view on this tables and it is taking more than 3hrs to return the results. Can someone help me the way to improve the db

Re: [PERFORM] seq scan in the case of max() on the primary key column

2011-06-22 Thread Marti Raudsepp
On Thu, Jun 16, 2011 at 21:36, Shaun Thomas stho...@peak6.com wrote: You can call that instead of max, and it'll be much faster. You can create an analog for min if you need it. So for this, you'd call: Cool, I've needed this function sometimes but never bothered enough to write it myself. Now

Re: [PERFORM] seq scan in the case of max() on the primary key column

2011-06-22 Thread Shaun Thomas
On 06/22/2011 04:55 AM, Marti Raudsepp wrote: With Jim Nasby's idea to use regclass instead of relation names, the function is now half its length and probably more reliable. There's no need to touch pg_class directly at all. Sadly until we upgrade to EDB 9.0, I have to use my function. :)

Re: [PERFORM] Contemplating SSD Hardware RAID

2011-06-22 Thread Shaun Thomas
On 06/21/2011 05:17 PM, Greg Smith wrote: If they just do the same style of write cache and reliability rework to the enterprise line, but using better flash, I agree that the first really serious yet affordable product for the database market may finally come out of that. After we started

Re: [PERFORM] seq scan in the case of max() on the primary key column

2011-06-22 Thread Greg Smith
On 06/22/2011 05:55 AM, Marti Raudsepp wrote: Now I created a wiki snippet page for this handy feature here: https://wiki.postgresql.org/wiki/Efficient_min/max_over_partitioned_table I just tweaked this a bit to document the version compatibility issues around it and make it easier to

Re: [PERFORM] seq scan in the case of max() on the primary key column

2011-06-22 Thread Scott Marlowe
On Wed, Jun 22, 2011 at 7:12 AM, Shaun Thomas stho...@peak6.com wrote: On 06/22/2011 04:55 AM, Marti Raudsepp wrote: With Jim Nasby's idea to use regclass instead of relation names, the function is now half its length and probably more reliable. There's no need to touch pg_class directly at

Re: [PERFORM] seq scan in the case of max() on the primary key column

2011-06-22 Thread Shaun Thomas
On 06/22/2011 01:12 PM, Scott Marlowe wrote: Given that many folks still run 9.0 in production, the wiki page should really have a version of that function for older versions, whether it's long or not. This version does work on anything 8.3 and above. I just lamented on 9.0 because we

[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] seq scan in the case of max() on the primary key column

2011-06-22 Thread Greg Smith
On 06/22/2011 02:12 PM, Scott Marlowe wrote: Given that many folks still run 9.0 in production, the wiki page should really have a version of that function for older versions, whether it's long or not. I updated the page already to be clear about what versions of PostgreSQL it works on,

Re: [PERFORM] bitmask index

2011-06-22 Thread Greg Smith
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: