Re: [PERFORM] There is a statistic table?
waldomiro wrote: I need to know how much the postgres is going to disk to get blocks and how much it is going to cache? witch is the statistic table and what is the field that indicates blocks reads from the disk and the memory cache? The view pg_statio_all_tables will show you the number of disk reads and buffer hits per table. There are other statistics views, see http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS Another question is, what is the best memory configuration to keep more data in cache? That's easy - the greater shared_buffers is, the more cache you have. Another option is to choose shared_buffers not too large and let the filesystem cache buffer the database for you. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UUID as primary key
On Oct 10, 2009, at 10:40 AM, Mark Mielke wrote: On 10/10/2009 01:14 AM, tsuraan wrote: The most significant impact is that it takes up twice as much space, including the primary key index. This means fewer entries per block, which means slower scans and/or more blocks to navigate through. Still, compared to the rest of the overhead of an index row or a table row, it is low - I think it's more important to understand whether you can get away with using a sequential integer, in which case UUID is unnecessary overhead - or whether you are going to need UUID anyways. If you need UUID anyways - having two primary keys is probably not worth it. Ok, that's what I was hoping. Out of curiosity, is there a preferred way to store 256-bit ints in postgres? At that point, is a bytea the most reasonable choice, or is there a better way to do it? Do you need to be able to do queries on it? Numeric should be able to store 256-bit integers. If you don't need to do queries on it, an option I've considered in the past is to break it up into 4 x int64. Before UUID was supported, I had seriously considered storing UUID as 2 x int64. Now that UUID is supported, you might also abuse UUID where 1 x 256- bit = 2 x UUID. If you want it to be seemless and fully optimal, you would introduce a new int256 type (or whatever the name of the type you are trying to represent). Adding new types to PostgreSQL is not that hard. This would allow queries (=, , , ) as well. If you want an example of that, we had Command Prompt create a full set of hash datatypes (SHA*, and I think md5). That stuff should be on pgFoundry; if it's not drop me a note at jna...@cashnetusa.com and I'll get it added. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UUID as primary key
decibel escribió: If you want it to be seemless and fully optimal, you would introduce a new int256 type (or whatever the name of the type you are trying to represent). Adding new types to PostgreSQL is not that hard. This would allow queries (=, , , ) as well. If you want an example of that, we had Command Prompt create a full set of hash datatypes (SHA*, and I think md5). That stuff should be on pgFoundry; if it's not drop me a note at jna...@cashnetusa.com and I'll get it added. It's at project shatypes. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Indexes on low cardinality columns
Folks, We have just migrated from Oracle to PG. We have a database that has approx 3 mil rows and one of the columns has a cardinality of only 0.1% (3000 unique values). We have to issue several queries that use this low cardinality column in a WHERE clause as well as see this column participating in JOINS (ouch!). A regular B-Tree index has been created on these columns. In Oracle, we replaced the B-Tree Indexes with Bitmap indexes and saw performance go through the roof. I know Postgres does not have Bitmap indexes, but is there a reasonable alternative to boost performance in situations where low cardinality columns are involved ? I dont have the option of changing schemas - so please dont go there :) TIA, VK
Re: [PERFORM] Indexes on low cardinality columns
On Fri, Oct 16, 2009 at 4:36 PM, Vikul Khosla vkho...@gridsolv.com wrote: In Oracle, we replaced the B-Tree Indexes with Bitmap indexes and saw performance go through the roof. I know Postgres does not have Bitmap indexes, but is there a reasonable alternative to boost performance in situations where low cardinality columns are involved ? Do you need to query on all of the 3,000 values? If it's just particular values which are common i would suggest using partial indexes on some other column with a where clause restricting them to only one value in the low-cardinality column. But I wouldn't want to have 3,000 indexes. Alternately you could try partitioning the table, though 3,000 partitions is a lot too. If you often update this value then partitioning wouldn't work well anyways (but then bitmap indexes wouldn't have worked well in oracle either) -- greg -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] UUID as primary key
On Oct 10, 2009, at 10:40 AM, Mark Mielke wrote: On 10/10/2009 01:14 AM, tsuraan wrote: The most significant impact is that it takes up twice as much space, including the primary key index. This means fewer entries per block, which means slower scans and/or more blocks to navigate through. Still, compared to the rest of the overhead of an index row or a table row, it is low - I think it's more important to understand whether you can get away with using a sequential integer, in which case UUID is unnecessary overhead - or whether you are going to need UUID anyways. If you need UUID anyways - having two primary keys is probably not worth it. Ok, that's what I was hoping. Out of curiosity, is there a preferred way to store 256-bit ints in postgres? At that point, is a bytea the most reasonable choice, or is there a better way to do it? Do you need to be able to do queries on it? Numeric should be able to store 256-bit integers. If you don't need to do queries on it, an option I've considered in the past is to break it up into 4 x int64. Before UUID was supported, I had seriously considered storing UUID as 2 x int64. Now that UUID is supported, you might also abuse UUID where 1 x 256- bit = 2 x UUID. If you want it to be seemless and fully optimal, you would introduce a new int256 type (or whatever the name of the type you are trying to represent). Adding new types to PostgreSQL is not that hard. This would allow queries (=, , , ) as well. If you want an example of that, we had Command Prompt create a full set of hash datatypes (SHA*, and I think md5). That stuff should be on pgFoundry; if it's not drop me a note at jna...@cashnetusa.com and I'll get it added. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
