[PERFORM] UNION ALL vs INHERITANCE
@@ '\'mmcach\''::tsquery) - Subquery Scan *SELECT* 2 (cost=0.00..13.92 rows=1 width=72) (actual time=1.146..1.146 rows=0 loops=1) - Index Scan using forfallnewgrgfid on for_f_all_new f_f_all_new (cost=0.00..13.91 rows=1 width=72) (actual time=1.135..1.135 rows=0 loops=1) Index Cond: (group_id = 78745) Filter: (all_tidx @@ '\'mmcach\''::tsquery) Total runtime: 82.108 ms (13 rows) -- Adi Alurkar (DBA sf.NET) [EMAIL PROTECTED] 1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] Dump/Restore performance improvement
Greetings, I have observed that in a dump/restore scenario the longest time is spent on index creation for larger tables, I have a suggestion of how the performance could be improved thus reducing the time to recover from a crash. Not sure if this is possible but would definitely be a nice addition to the TODO list. 1) Add a new config paramter e.g work_maintanence_max_mem this will the max memory postgresql *can* claim if need be. 2) During the dump phase of the DB postgresql estimates the work_maintenance_mem that would be required to create the index in memory(if possible) and add's a SET work_maintenance_mem=the value calculated (IF this value is less than work_maintanence_max_mem. ) 3) During the restore phase the appropriate memory is allocated in RAM and the index creation takes less time since PG does not have to sort on disk. -- Adi Alurkar (DBA sf.NET) [EMAIL PROTECTED] 1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?
Greetings, I am not sure if this applies only to clustering but for storage in general, IIRC Oracle has 2 parameters that can be set at table creation : from Oracle docs PCTFREE integer : Specify the percentage of space in each data block of the table, object table OID index, or partition reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block. PCTFREE has the same function in the PARTITION description and in the statements that create and alter clusters, indexes, materialized views, and materialized view logs. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks. PCTUSED integer Specify the minimum percentage of used space that Oracle maintains for each data block of the table, object table OID index, or index-organized table overflow data segment. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40. PCTUSED has the same function in the PARTITION description and in the statements that create and alter clusters, materialized views, and materialized view logs. PCTUSED is not a valid table storage characteristic for an index-organized table (ORGANIZATION INDEX). The sum of PCTFREE and PCTUSED must be equal to or less than 100. You can use PCTFREE and PCTUSED together to utilize space within a table more efficiently. PostgreSQL could take some hints from the above. On Aug 27, 2004, at 1:26 AM, Gaetano Mendola wrote: Greg Stark wrote: The discussions before talked about a mechanism to try to place new tuples as close as possible to the proper index position. Means this that an index shall have a fill factor property, similar to Informix one ? From the manual: The FILLFACTOR option takes effect only when you build an index on a table that contains more than 5,000 rows and uses more than 100 table pages, when you create an index on a fragmented table, or when you create a fragmented index on a nonfragmented table. Use the FILLFACTOR option to provide for expansion of an index at a later date or to create compacted indexes. When the index is created, the database server initially fills only that percentage of the nodes specified with the FILLFACTOR value. # Providing a Low Percentage Value If you provide a low percentage value, such as 50, you allow room for growth in your index. The nodes of the index initially fill to a certain percentage and contain space for inserts. The amount of available space depends on the number of keys in each page as well as the percentage value. For example, with a 50-percent FILLFACTOR value, the page would be half full and could accommodate doubling in size. A low percentage value can result in faster inserts and can be used for indexes that you expect to grow. # Providing a High Percentage Value If you provide a high percentage value, such as 99, your indexes are compacted, and any new index inserts result in splitting nodes. The maximum density is achieved with 100 percent. With a 100-percent FILLFACTOR value, the index has no room available for growth; any additions to the index result in splitting the nodes. A 99-percent FILLFACTOR value allows room for at least one insertion per node. A high percentage value can result in faster selects and can be used for indexes that you do not expect to grow or for mostly read-only indexes. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Adi Alurkar (DBA sf.NET) [EMAIL PROTECTED] 1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Equivalent praxis to CLUSTERED INDEX?
IIRC it it to reduce the overflow of data or what oracle calls chained rows. i.e if a table has variable length columns and 10 rows get inserted into a datapage, if this datapage is full and one of the variable length field gets updated the row will now overflow into another datapage, but if the datapage is created with an appropriate amount of free space the updated row will be stored in one single datapage. On Aug 27, 2004, at 10:27 AM, Bruce Momjian wrote: But what is the advantage of non-full pages in Oracle? --- Adi Alurkar wrote: Greetings, I am not sure if this applies only to clustering but for storage in general, IIRC Oracle has 2 parameters that can be set at table creation : from Oracle docs PCTFREE integer : Specify the percentage of space in each data block of the table, object table OID index, or partition reserved for future updates to the table's rows. The value of PCTFREE must be a value from 0 to 99. A value of 0 allows the entire block to be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block. PCTFREE has the same function in the PARTITION description and in the statements that create and alter clusters, indexes, materialized views, and materialized view logs. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks. PCTUSED integer Specify the minimum percentage of used space that Oracle maintains for each data block of the table, object table OID index, or index-organized table overflow data segment. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40. PCTUSED has the same function in the PARTITION description and in the statements that create and alter clusters, materialized views, and materialized view logs. PCTUSED is not a valid table storage characteristic for an index-organized table (ORGANIZATION INDEX). The sum of PCTFREE and PCTUSED must be equal to or less than 100. You can use PCTFREE and PCTUSED together to utilize space within a table more efficiently. PostgreSQL could take some hints from the above. On Aug 27, 2004, at 1:26 AM, Gaetano Mendola wrote: Greg Stark wrote: The discussions before talked about a mechanism to try to place new tuples as close as possible to the proper index position. Means this that an index shall have a fill factor property, similar to Informix one ? From the manual: The FILLFACTOR option takes effect only when you build an index on a table that contains more than 5,000 rows and uses more than 100 table pages, when you create an index on a fragmented table, or when you create a fragmented index on a nonfragmented table. Use the FILLFACTOR option to provide for expansion of an index at a later date or to create compacted indexes. When the index is created, the database server initially fills only that percentage of the nodes specified with the FILLFACTOR value. # Providing a Low Percentage Value If you provide a low percentage value, such as 50, you allow room for growth in your index. The nodes of the index initially fill to a certain percentage and contain space for inserts. The amount of available space depends on the number of keys in each page as well as the percentage value. For example, with a 50-percent FILLFACTOR value, the page would be half full and could accommodate doubling in size. A low percentage value can result in faster inserts and can be used for indexes that you expect to grow. # Providing a High Percentage Value If you provide a high percentage value, such as 99, your indexes are compacted, and any new index inserts result in splitting nodes. The maximum density is achieved with 100 percent. With a 100-percent FILLFACTOR value, the index has no room available for growth; any additions to the index result in splitting the nodes. A 99-percent FILLFACTOR value allows room for at least one insertion per node. A high percentage value can result in faster selects and can be used for indexes that you do not expect to grow or for mostly read-only indexes. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Adi Alurkar (DBA sf.NET) [EMAIL PROTECTED] 1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup
