Re: Possible to improve query plan?

От: Jeremy Palmer
Тема: Re: Possible to improve query plan?
Дата: ,
Msg-id: 666FB8D75E95AE42965A0E76A5E5337E06D2D27965@prdlsmmsg01.ad.linz.govt.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Possible to improve query plan?  ("Kevin Grittner")
Ответы: Re: Possible to improve query plan?  ("Kevin Grittner")
Re: Possible to improve query plan?  ("Kevin Grittner")
Список: pgsql-performance

Скрыть дерево обсуждения

Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )

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 ) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________


В списке pgsql-performance по дате сообщения:

От: Josh Berkus
Дата:
Сообщение: Bloat issue on 8.3; autovac ignores HOT page splits?
От: Dimi Paun
Дата:
Сообщение: Re: How to use indexes for GROUP BY