Re: Strangely Variable Query Performance - Mailing list pgsql-performance
From | Steve |
---|---|
Subject | Re: Strangely Variable Query Performance |
Date | |
Msg-id | Pine.GSO.4.64.0704121735000.17955@kittyhawk.tanabi.org Whole thread Raw |
In response to | Re: Strangely Variable Query Performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Strangely Variable Query Performance
|
List | pgsql-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)
pgsql-performance by date: