Skip site navigation (1) Skip section navigation (2)

Re: Strangely Variable Query Performance

From: Steve <cheetah(at)tanabi(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Strangely Variable Query Performance
Date: 2007-04-12 21:40:28
Message-ID: Pine.GSO.4.64.0704121735000.17955@kittyhawk.tanabi.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patchespgsql-performance
> 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)


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2007-04-12 21:52:04
Subject: Re: Strangely Variable Query Performance
Previous:From: Tom LaneDate: 2007-04-12 21:24:52
Subject: Re: Strangely Variable Query Performance

pgsql-hackers by date

Next:From: Scott MarloweDate: 2007-04-12 21:52:04
Subject: Re: Strangely Variable Query Performance
Previous:From: Tom LaneDate: 2007-04-12 21:24:52
Subject: Re: Strangely Variable Query Performance

pgsql-patches by date

Next:From: Scott MarloweDate: 2007-04-12 21:52:04
Subject: Re: Strangely Variable Query Performance
Previous:From: Tom LaneDate: 2007-04-12 21:24:52
Subject: Re: Strangely Variable Query Performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group