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: http://www.postgresql.org/docs/current/static/indexes-partia

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,

[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 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 decid

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 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 all. > > Sa

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 foll

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 our

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. :) EDB

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 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 I created a wiki

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 th