Re: Strangely Variable Query 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)