Re: Possible to improve query plan? - Mailing list pgsql-performance

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
needto re-cluster the table, because it comes at quite a cost. The setup I'm running will mean that 10,000 new rows
willbe 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@linz.govt.nz) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________