Re: Strangely Variable Query Performance

[Steve <cheetah(at)tanabi(dot)org>]

> 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)