Re: Strangely Variable Query Performance

От: Steve
Тема: Re: Strangely Variable Query Performance
Дата: ,
Msg-id: Pine.GSO.4.64.0704121735000.17955@kittyhawk.tanabi.org
(см: обсуждение, исходный текст)
Ответ на: Re: Strangely Variable Query Performance  (Tom Lane)
Ответы: Re: Strangely Variable Query Performance  (Tom Lane)
Список: pgsql-performance

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

Slow Postgresql server  (Jason Lustig, )
 Re: Slow Postgresql server  (Dennis Bjorklund, )
 Re: Slow Postgresql server  (Jeff Frost, )
 Re: Slow Postgresql server  (Ron, )
  Re: Slow Postgresql server  (Guido Neitzer, )
   Re: Slow Postgresql server  (Ron, )
    Re: Slow Postgresql server  (Guido Neitzer, )
     Re: Slow Postgresql server  (Scott Marlowe, )
      Re: Slow Postgresql server  (Jeff Frost, )
       Re: Slow Postgresql server  (Carlos Moreno, )
       Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Tom Lane, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Tom Lane, )
           Re: Strangely Variable Query Performance  (Steve, )
            Re: Strangely Variable Query Performance  (Tom Lane, )
             Re: Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Scott Marlowe, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Scott Marlowe, )
           Re: Strangely Variable Query Performance  (Steve, )
           Re: Strangely Variable Query Performance  (Tom Lane, )
            Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
               Re: Strangely Variable Query Performance  (Tom Lane, )
                Re: Strangely Variable Query Performance  (Steve, )
                 Re: Strangely Variable Query Performance  (Tom Lane, )
                  Re: Strangely Variable Query Performance  (Steve, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                    Re: Strangely Variable Query Performance  (Steve, )
                     Re: Strangely Variable Query Performance  (Tom Lane, )
                      Fwd: Strangely Variable Query Performance  ("Robins Tharakan", )
                     choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                      Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Alvaro Herrera, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
 Re: Slow Postgresql server  (Jeff Frost, )
  Re: Slow Postgresql server  (Jason Lustig, )
   Re: Slow Postgresql server  (Guido Neitzer, )
 Fwd: Strangely Variable Query Performance  (Robins, )
  Re: Fwd: Strangely Variable Query Performance  (Tom Lane, )

> Could we see the exact definition of that table and its indexes?
> It looks like the planner is missing the bitmap scan for some reason,
> but I've not seen a case like that before.
>
> Also, I assume the restriction on receipt date is very nonselective?
> It doesn't seem to have changed the estimated rowcount much.
>

     This is true -- This particular receipt date is actually quite
meaningless.  It's equivalent to saying 'all receipt dates'.  I don't
think there's even any data that goes back before 2005.

Here's the table and it's indexes.  Before looking, a note; there's
several 'revop' indexes, this is for sorting.  The customer insisted on,
frankly, meaninglessly complicated sorts.  I don't think any of that
matters for our purposes here though :)

         Column         |          Type          |
Modifiers
-----------------------+------------------------+--------------------------------------------------------------------
  detailsummary_id      | integer                | not null default
nextval(('detailsummary_id_seq'::text)::regclass)
  detailgroup_id        | integer                |
  receipt               | date                   |
  batchnum              | integer                |
  encounternum          | integer                |
  procedureseq          | integer                |
  procedurecode         | character varying(5)   |
  wrong_procedurecode   | character varying(5)   |
  batch_id              | integer                |
  encounter_id          | integer                |
  procedure_id          | integer                |
  carrier_id            | integer                |
  product_line          | integer                |
  provider_id           | integer                |
  member_num            | character varying(20)  |
  wrong_member_num      | character varying(20)  |
  member_name           | character varying(40)  |
  patient_control       | character varying(20)  |
  rendering_prov_id     | character varying(15)  |
  rendering_prov_name   | character varying(30)  |
  referring_prov_id     | character varying(15)  |
  referring_prov_name   | character varying(30)  |
  servicedate           | date                   |
  wrong_servicedate     | date                   |
  diagnosis_codes       | character varying(5)[] |
  wrong_diagnosis_codes | character varying(5)[] |
  ffs_charge            | double precision       |
  export_date           | date                   |
  hedis_date            | date                   |
  raps_date             | date                   |
  diagnosis_pointers    | character(1)[]         |
  modifiers             | character(2)[]         |
  units                 | double precision       |
  pos                   | character(2)           |
  isduplicate           | boolean                |
  duplicate_id          | integer                |
  encounter_corrected   | boolean                |
  procedure_corrected   | boolean                |
  numerrors             | integer                |
  encerrors_codes       | integer[]              |
  procerror_code        | integer                |
  error_servicedate     | text                   |
  e_duplicate_id        | integer                |
  ecode_counts          | integer[]              |
  p_record_status       | integer                |
  e_record_status       | integer                |
  e_delete_date         | date                   |
  p_delete_date         | date                   |
  b_record_status       | integer                |
  b_confirmation        | character varying(20)  |
  b_carrier_cobol_id    | character varying(16)  |
  b_provider_cobol_id   | character varying(20)  |
  b_provider_tax_id     | character varying(16)  |
  b_carrier_name        | character varying(50)  |
  b_provider_name       | character varying(50)  |
  b_submitter_file_id   | character varying(40)  |
  e_hist_carrier_id     | integer                |
  p_hist_carrier_id     | integer                |
  e_duplicate_id_orig   | character varying(25)  |
  p_duplicate_id_orig   | character varying(25)  |
  num_procerrors        | integer                |
  num_encerrors         | integer                |
  export_id             | integer                |
  raps_id               | integer                |
  hedis_id              | integer                |
Indexes:
     "detail_summary_b_record_status_idx" btree (b_record_status)
     "detail_summary_batch_id_idx" btree (batch_id)
     "detail_summary_batchnum_idx" btree (batchnum)
     "detail_summary_carrier_id_idx" btree (carrier_id)
     "detail_summary_duplicate_id_idx" btree (duplicate_id)
     "detail_summary_e_record_status_idx" btree (e_record_status)
     "detail_summary_encounter_id_idx" btree (encounter_id)
     "detail_summary_encounternum_idx" btree (encounternum)
     "detail_summary_export_date_idx" btree (export_date)
     "detail_summary_hedis_date_idx" btree (hedis_date)
     "detail_summary_member_name_idx" btree (member_name)
     "detail_summary_member_num_idx" btree (member_num)
     "detail_summary_p_record_status_idx" btree (p_record_status)
     "detail_summary_patient_control_idx" btree (patient_control)
     "detail_summary_procedurecode_idx" btree (procedurecode)
     "detail_summary_product_line_idx" btree (product_line)
     "detail_summary_provider_id_idx" btree (provider_id)
     "detail_summary_raps_date_idx" btree (raps_date)
     "detail_summary_receipt_encounter_idx" btree (receipt, encounter_id)
     "detail_summary_receipt_id_idx" btree (receipt)
     "detail_summary_referrering_prov_id_idx" btree (referring_prov_id)
     "detail_summary_rendering_prov_id_idx" btree (rendering_prov_id)
     "detail_summary_rendering_prov_name_idx" btree (rendering_prov_name)
     "detail_summary_servicedate_idx" btree (servicedate)
     "ds_sort_1" btree (receipt date_revop, carrier_id, batchnum,
encounternum, procedurecode, encounter_id)
     "ds_sort_10" btree (receipt date_revop, carrier_id, batchnum,
encounternum, procedurecode, encounter_id, procedure_id)
     "ed_cbee_norev" btree (export_date, carrier_id, batchnum,
encounternum, encounter_id)
     "ed_cbee_norev_p" btree (export_date, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "ed_cbee_rev" btree (export_date date_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "ed_cbee_rev_p" btree (export_date date_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "mcbe" btree (member_name, carrier_id, batchnum, encounternum,
encounter_id)
     "mcbe_p" btree (member_name, carrier_id, batchnum, encounternum,
encounter_id, procedure_id)
     "mcbe_rev" btree (member_name text_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "mcbe_rev_p" btree (member_name text_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "mcbee_norev" btree (member_num, carrier_id, batchnum, encounternum,
encounter_id)
     "mcbee_norev_p" btree (member_num, carrier_id, batchnum, encounternum,
encounter_id, procedure_id)
     "mcbee_rev" btree (member_num text_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "mcbee_rev_p" btree (member_num text_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "pcbee_norev" btree (patient_control, carrier_id, batchnum,
encounternum, encounter_id)
     "pcbee_norev_p" btree (patient_control, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "pcbee_rev" btree (patient_control text_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "pcbee_rev_p" btree (patient_control text_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "rcbee_norev" btree (receipt, carrier_id, batchnum, encounternum,
encounter_id)
     "rcbee_norev_p" btree (receipt, carrier_id, batchnum, encounternum,
encounter_id, procedure_id)
     "rp_cbee_norev" btree (rendering_prov_name, carrier_id, batchnum,
encounternum, encounter_id)
     "rp_cbee_norev_p" btree (rendering_prov_name, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "rp_cbee_rev" btree (rendering_prov_name text_revop, carrier_id,
batchnum, encounternum, encounter_id)
     "rp_cbee_rev_p" btree (rendering_prov_name text_revop, carrier_id,
batchnum, encounternum, encounter_id, procedure_id)
     "sd_cbee_norev" btree (servicedate, carrier_id, batchnum,
encounternum, encounter_id)
     "sd_cbee_norev_p" btree (servicedate, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "sd_cbee_rev" btree (servicedate date_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "sd_cbee_rev_p" btree (servicedate date_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)
     "testrev" btree (receipt date_revop, carrier_id, batchnum,
encounternum, encounter_id)
     "testrev_p" btree (receipt date_revop, carrier_id, batchnum,
encounternum, encounter_id, procedure_id)



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

От: Greg Smith
Дата:
Сообщение: Re: Question about memory allocations
От: Tom Lane
Дата:
Сообщение: Re: Strangely Variable Query Performance