Might be a chance on 9.0 in a couple of weeks, when I do an upgrade on one our dev boxes.
Kevin I've now clustered the table. And the performance did increase quite a bit. My only question is how often will I need to re-cluster the table, because it comes at quite a cost. The setup I'm running will mean that 10,000 new rows will be inserted, and 2,500 rows will be updated on this table each day.
Here is the new explain output once I have clustered on the idx_crs_coordinate_revision_created index:
Subquery Scan t (cost=168227.04..173053.88 rows=743 width=205) (actual time=392.586..946.879 rows=106299 loops=1)
Output: t.row_number, t._revision_created, t._revision_expired, t.id, t.cos_id, t.nod_id, t.ort_type_1, t.ort_type_2, t.ort_type_3, t.status, t.sdc_status, t.source, t.value1, t.value2, t.value3, t.wrk_id_created, t.cor_id, t.audit_id
Filter: (t.row_number = 1)
-> WindowAgg (cost=168227.04..171197.40 rows=148518 width=86) (actual time=392.577..834.477 rows=149557 loops=1)
Output: row_number() OVER (?), table_version_crs_coordinate_revision._revision_created, table_version_crs_coordinate_revision._revision_expired, table_version_crs_coordinate_revision.id, table_version_crs_coordinate_revision.cos_id, table_version_crs_coordinate_revision.nod_id, table_version_crs_coordinate_revision.ort_type_1, table_version_crs_coordinate_revision.ort_type_2, table_version_crs_coordinate_revision.ort_type_3, table_version_crs_coordinate_revision.status, table_version_crs_coordinate_revision.sdc_status, table_version_crs_coordinate_revision.source, table_version_crs_coordinate_revision.value1, table_version_crs_coordinate_revision.value2, table_version_crs_coordinate_revision.value3, table_version_crs_coordinate_revision.wrk_id_created, table_version_crs_coordinate_revision.cor_id, table_version_crs_coordinate_revision.audit_id
-> Sort (cost=168227.04..168598.34 rows=148518 width=86) (actual time=392.550..457.460 rows=149557 loops=1)
Output: table_version_crs_coordinate_revision._revision_created, table_version_crs_coordinate_revision._revision_expired, table_version_crs_coordinate_revision.id, table_version_crs_coordinate_revision.cos_id, table_version_crs_coordinate_revision.nod_id, table_version_crs_coordinate_revision.ort_type_1, table_version_crs_coordinate_revision.ort_type_2, table_version_crs_coordinate_revision.ort_type_3, table_version_crs_coordinate_revision.status, table_version_crs_coordinate_revision.sdc_status, table_version_crs_coordinate_revision.source, table_version_crs_coordinate_revision.value1, table_version_crs_coordinate_revision.value2, table_version_crs_coordinate_revision.value3, table_version_crs_coordinate_revision.wrk_id_created, table_version_crs_coordinate_revision.cor_id, table_version_crs_coordinate_revision.audit_id
Sort Key: table_version_crs_coordinate_revision.id, table_version_crs_coordinate_revision._revision_created
Sort Method: quicksort Memory: 23960kB
-> Bitmap Heap Scan on table_version_crs_coordinate_revision (cost=3215.29..155469.14 rows=148518 width=86) (actual time=38.808..196.993 rows=149557 loops=1)
Output: table_version_crs_coordinate_revision._revision_created, table_version_crs_coordinate_revision._revision_expired, table_version_crs_coordinate_revision.id, table_version_crs_coordinate_revision.cos_id, table_version_crs_coordinate_revision.nod_id, table_version_crs_coordinate_revision.ort_type_1, table_version_crs_coordinate_revision.ort_type_2, table_version_crs_coordinate_revision.ort_type_3, table_version_crs_coordinate_revision.status, table_version_crs_coordinate_revision.sdc_status, table_version_crs_coordinate_revision.source, table_version_crs_coordinate_revision.value1, table_version_crs_coordinate_revision.value2, table_version_crs_coordinate_revision.value3, table_version_crs_coordinate_revision.wrk_id_created, table_version_crs_coordinate_revision.cor_id, table_version_crs_coordinate_revision.audit_id
Recheck Cond: (((_revision_expired > 16) AND (_revision_expired <= 40)) OR ((_revision_created > 16) AND (_revision_created <= 40)))
Filter: (((_revision_created <= 16) AND (_revision_expired > 16) AND (_revision_expired <= 40)) OR ((_revision_created > 16) AND (_revision_created <= 40)))
-> BitmapOr (cost=3215.29..3215.29 rows=149432 width=0) (actual time=27.330..27.330 rows=0 loops=1)
-> Bitmap Index Scan on idx_crs_coordinate_revision_expired (cost=0.00..2225.36 rows=106001 width=0) (actual time=21.596..21.596 rows=110326 loops=1)
Index Cond: ((_revision_expired > 16) AND (_revision_expired <= 40))
-> Bitmap Index Scan on idx_crs_coordinate_revision_created (cost=0.00..915.67 rows=43432 width=0) (actual time=5.728..5.728 rows=43258 loops=1)
Index Cond: ((_revision_created > 16) AND (_revision_created <= 40))
Total runtime: 985.671 ms
Thanks heaps,
Jeremy
______________________________________________________________________________________________________
This message contains information, which is confidential and may be subject to legal privilege.
If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message.
If you have received this message in error, please notify us immediately (Phone 0800 665 463 or info@xxxxxxxxxxxx) and destroy the original message.
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.
Thank you.
______________________________________________________________________________________________________
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
