pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrangely Variable Query Performance
29+ messages / 5 participants
[nested] [flat]
* Strangely Variable Query Performance
@ 2007-04-12 21:03 Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 2 replies; 29+ messages in thread
From: Steve @ 2007-04-12 21:03 UTC (permalink / raw)
To: pgsql-performance
Hey there;
On a Postgres 8.2.3 server, I've got a query that is running very slow in
some cases. With some work, I've determined the 'slow part' of the query.
:) This is a query on a table with like 10 million rows or something like
that. encounter_id is an integer and receipt is of type 'date'.
This query runs really slow [minutes] (query and explain below):
select extract(epoch from ds.receipt) from detail_summary ds where
ds.receipt >= '1998-12-30 0:0:0' and
ds.encounter_id in
(8813186,8813187,8813188,8813189,8813190,8813191,8813192,
8813193,8813194,8813195,8813196,8813197,8813198,8813199,
8813200,8813201,8813202,8813203,8813204,8813205,8813206,
8813207,8813208,8813209,8813210,8813211,8813212,8813213,
8813214,8813215,8813216,8813217,8813218,8813219,8813220,
8813221,8813222,8813223,8813224,8813225,8813226,8813227,
8813228,8813229,8813230,8813231,8813232,8813233,8813234,
8813235,8813236,8813237,8813238,8813239,8813240,8813241,
8813242,8813243,8813244,8813245,8813246,8813247,8813248,
8813249,8813250,8813251,8813252,8813253,8813254,8813255,
8813256,8813257,8813258,8813259,8813260,8813261,8813262,
8813263,8813264,8813265,8813266,8813267,8813268,8813269,
8813270,8813271,8813272,8813273,8813274,8813275,8813276,
8813277,8813278,8813279,8813280,8813281,8813282,8813283,
8813284,8815534)
Results in the 'explain' :
Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
(2 rows)
Turning enable_seqscan to off results in a slightly more interesting
explain, but an equally slow query.
HOWEVER! The simple removal of the receipt date paramater results in a
fast query, as such:
select extract(epoch from ds.receipt) from detail_summary ds where
ds.encounter_id in
(8813186,8813187,8813188,8813189,8813190,8813191,8813192,
8813193,8813194,8813195,8813196,8813197,8813198,8813199,
8813200,8813201,8813202,8813203,8813204,8813205,8813206,
8813207,8813208,8813209,8813210,8813211,8813212,8813213,
8813214,8813215,8813216,8813217,8813218,8813219,8813220,
8813221,8813222,8813223,8813224,8813225,8813226,8813227,
8813228,8813229,8813230,8813231,8813232,8813233,8813234,
8813235,8813236,8813237,8813238,8813239,8813240,8813241,
8813242,8813243,8813244,8813245,8813246,8813247,8813248,
8813249,8813250,8813251,8813252,8813253,8813254,8813255,
8813256,8813257,8813258,8813259,8813260,8813261,8813262,
8813263,8813264,8813265,8813266,8813267,8813268,8813269,
8813270,8813271,8813272,8813273,8813274,8813275,8813276,
8813277,8813278,8813279,8813280,8813281,8813282,8813283,
8813284 ,8815534)
This query returns instantly and explains as:
Bitmap Heap Scan on detail_summary ds (cost=161.00..14819.81 rows=9963
width=4)
Recheck Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
-> Bitmap Index Scan on detail_summary_encounter_id_idx
(cost=0.00..160.75 rows=9963 width=0)
Index Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
Any thoughts? Both encounter_id and receipt date are indexed columns.
I've vacuumed and analyzed the table. I tried making a combined index of
encounter_id and receipt and it hasn't worked out any better.
Thanks!
Steve
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 21:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-12 21:24 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
Steve <cheetah@tanabi.org> writes:
> On a Postgres 8.2.3 server, I've got a query that is running very slow in
> some cases.
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.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 21:40 ` Steve <cheetah@tanabi.org>
2007-04-12 22:01 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Steve @ 2007-04-12 21:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 22:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:03 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-12 22:01 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
Steve <cheetah@tanabi.org> writes:
> 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 :)
Oy vey ... I hope this is a read-mostly table, because having that many
indexes has got to be killing your insert/update performance.
I see that some of the revop indexes might be considered relevant to
this query, so how exactly have you got those opclasses defined?
There's built-in support for reverse sort as of CVS HEAD, but in
existing releases you must have cobbled something together, and I wonder
if that could be a contributing factor ...
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:01 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 22:03 ` Steve <cheetah@tanabi.org>
2007-04-12 22:22 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Steve @ 2007-04-12 22:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
>
> Oy vey ... I hope this is a read-mostly table, because having that many
> indexes has got to be killing your insert/update performance.
Hahaha yeah these are read-only tables. Nightly inserts/updates.
Takes a few hours, depending on how many records (between 4 and 10
usually). But during the day, while querying, read only.
> I see that some of the revop indexes might be considered relevant to
> this query, so how exactly have you got those opclasses defined?
> There's built-in support for reverse sort as of CVS HEAD, but in
> existing releases you must have cobbled something together, and I wonder
> if that could be a contributing factor ...
Here's the revops (the c functions are at the bottom):
CREATE FUNCTION ddd_date_revcmp(date, date) RETURNS integer
AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_date_revcmp'
LANGUAGE c STRICT;
CREATE FUNCTION ddd_int_revcmp(integer, integer) RETURNS integer
AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_int_revcmp'
LANGUAGE c STRICT;
CREATE FUNCTION ddd_text_revcmp(text, text) RETURNS integer
AS '/usr/local/pgsql/contrib/cmplib.so', 'ddd_text_revcmp'
LANGUAGE c STRICT;
CREATE OPERATOR CLASS date_revop
FOR TYPE date USING btree AS
OPERATOR 1 >(date,date) ,
OPERATOR 2 >=(date,date) ,
OPERATOR 3 =(date,date) ,
OPERATOR 4 <=(date,date) ,
OPERATOR 5 <(date,date) ,
FUNCTION 1 ddd_date_revcmp(date,date);
CREATE OPERATOR CLASS int4_revop
FOR TYPE integer USING btree AS
OPERATOR 1 >(integer,integer) ,
OPERATOR 2 >=(integer,integer) ,
OPERATOR 3 =(integer,integer) ,
OPERATOR 4 <=(integer,integer) ,
OPERATOR 5 <(integer,integer) ,
FUNCTION 1 ddd_int_revcmp(integer,integer);
CREATE OPERATOR CLASS text_revop
FOR TYPE text USING btree AS
OPERATOR 1 >(text,text) ,
OPERATOR 2 >=(text,text) ,
OPERATOR 3 =(text,text) ,
OPERATOR 4 <=(text,text) ,
OPERATOR 5 <(text,text) ,
FUNCTION 1 ddd_text_revcmp(text,text);
Datum ddd_date_revcmp(PG_FUNCTION_ARGS){
DateADT arg1=PG_GETARG_DATEADT(0);
DateADT arg2=PG_GETARG_DATEADT(1);
PG_RETURN_INT32(arg2 - arg1);
}
Datum ddd_int_revcmp(PG_FUNCTION_ARGS){
int32 arg1=PG_GETARG_INT32(0);
int32 arg2=PG_GETARG_INT32(1);
PG_RETURN_INT32(arg2 - arg1);
}
Datum ddd_text_revcmp(PG_FUNCTION_ARGS){
char* arg1=(char*)VARDATA(PG_GETARG_TEXT_P(0));
char* arg2=(char*)VARDATA(PG_GETARG_TEXT_P(1));
if((*arg1) != (*arg2)){
PG_RETURN_INT32(*arg2 - *arg1);
}else{
PG_RETURN_INT32(strcmp(arg2,arg1));
}
}
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:01 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:03 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:24 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-12 22:22 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
Steve <cheetah@tanabi.org> writes:
> Datum ddd_text_revcmp(PG_FUNCTION_ARGS){
> char* arg1=(char*)VARDATA(PG_GETARG_TEXT_P(0));
> char* arg2=(char*)VARDATA(PG_GETARG_TEXT_P(1));
> if((*arg1) != (*arg2)){
> PG_RETURN_INT32(*arg2 - *arg1);
> }else{
> PG_RETURN_INT32(strcmp(arg2,arg1));
> }
> }
[ itch... ] That code is just completely wrong, because the contents
of a TEXT datum aren't guaranteed null-terminated. It'd be better to
invoke bttextcmp and negate its result.
That's not relevant to your immediate problem, but if you've noticed
any strange behavior with your text_revop indexes, that's the reason...
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:01 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:03 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:22 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 22:24 ` Steve <cheetah@tanabi.org>
0 siblings, 0 replies; 29+ messages in thread
From: Steve @ 2007-04-12 22:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
> [ itch... ] That code is just completely wrong, because the contents
> of a TEXT datum aren't guaranteed null-terminated. It'd be better to
> invoke bttextcmp and negate its result.
>
> That's not relevant to your immediate problem, but if you've noticed
> any strange behavior with your text_revop indexes, that's the reason...
The indexes have all worked, though I'll make the change anyway.
Documentation on how to code these things is pretty sketchy and I believe
I followed an example on the site if I remember right. :/
Thanks for the info though :)
Steve
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 21:52 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
1 sibling, 1 reply; 29+ messages in thread
From: Scott Marlowe @ 2007-04-12 21:52 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
On Thu, 2007-04-12 at 16:03, Steve wrote:
> Hey there;
>
> On a Postgres 8.2.3 server, I've got a query that is running very slow in
> some cases. With some work, I've determined the 'slow part' of the query.
> :) This is a query on a table with like 10 million rows or something like
> that. encounter_id is an integer and receipt is of type 'date'.
SNIP
> Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
> Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
> ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
> (2 rows)
How accurate is the row estimate made by the planner? (explain analyze
to be sure)
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-04-12 22:04 ` Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 29+ messages in thread
From: Steve @ 2007-04-12 22:04 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
>> Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
>> Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
>> ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
>> (2 rows)
>
> How accurate is the row estimate made by the planner? (explain analyze
> to be sure)
>
Results:
Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
(actual time=62871.386..257258.249 rows=112 loops=1)
Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
Total runtime: 257258.652 ms
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 22:14 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:41 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 29+ messages in thread
From: Scott Marlowe @ 2007-04-12 22:14 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: pgsql-performance
On Thu, 2007-04-12 at 17:04, Steve wrote:
> >> Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
> >> Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
> >> ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
> >> (2 rows)
> >
> > How accurate is the row estimate made by the planner? (explain analyze
> > to be sure)
> >
>
> Results:
>
> Seq Scan on detail_summary ds (cost=0.00..1902749.83 rows=9962 width=4)
> (actual time=62871.386..257258.249 rows=112 loops=1)
> Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
> ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
> Total runtime: 257258.652 ms
So there's a misjudgment of the number of rows returned by a factor of
about 88. That's pretty big. Since you had the same number without the
receipt date (I think...) then it's the encounter_id that's not being
counted right.
Try upping the stats target on that column and running analyze again and
see if you get closer to 112 in your analyze or not.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-04-12 22:41 ` Steve <cheetah@tanabi.org>
1 sibling, 0 replies; 29+ messages in thread
From: Steve @ 2007-04-12 22:41 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-performance
>
> So there's a misjudgment of the number of rows returned by a factor of
> about 88. That's pretty big. Since you had the same number without the
> receipt date (I think...) then it's the encounter_id that's not being
> counted right.
>
> Try upping the stats target on that column and running analyze again and
> see if you get closer to 112 in your analyze or not.
>
If I max the statistics targets at 1000, I get:
Seq Scan on detail_summary ds (cost=0.00..1903030.26 rows=1099 width=4)
Filter: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
Setting it ot 500 makes the estimated rows twice as much. It
seems to have no effect on anything though, either way. :)
Steve
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
@ 2007-04-12 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-12 23:00 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Steve <cheetah@tanabi.org>; pgsql-performance
Scott Marlowe <smarlowe@g2switchworks.com> writes:
> So there's a misjudgment of the number of rows returned by a factor of
> about 88. That's pretty big. Since you had the same number without the
> receipt date (I think...) then it's the encounter_id that's not being
> counted right.
I don't think that's Steve's problem, though. It's certainly
misestimating, but nonetheless the cost estimate for the seqscan is
1902749.83 versus 14819.81 for the bitmap scan; it should've picked
the bitmap scan anyway.
I tried to duplicate the problem here, without any success; I get
Bitmap Heap Scan on detail_summary ds (cost=422.01..801.27 rows=100 width=4)
Recheck Cond: (encounter_id = ANY ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
Filter: (receipt >= '1998-12-30'::date)
-> Bitmap Index Scan on detail_summary_encounter_id_idx (cost=0.00..421.98 rows=100 width=0)
Index Cond: (encounter_id = ANY ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
so either this has been fixed by a post-8.2.3 bug fix (which I doubt,
it doesn't seem familiar at all) or there's some additional contributing
factor. Steve, are you using any nondefault planner parameters?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 22:56 ` Steve <cheetah@tanabi.org>
2007-04-12 23:29 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 29+ messages in thread
From: Steve @ 2007-04-12 22:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Here's my planner parameters:
seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 1.5 # same scale as above
cpu_tuple_cost = 0.001 # same scale as above
cpu_index_tuple_cost = 0.0005 # same scale as above
cpu_operator_cost = 0.00025 # same scale as above
effective_cache_size = 8192MB
default_statistics_target = 100 # range 1-1000
On a machine with 16 gigs of RAM. I tried to make it skew towards
indexes. However, even if I force it to use the indexes
(enable_seqscan=off) it doesn't make it any faster really :/
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.com> writes:
>> So there's a misjudgment of the number of rows returned by a factor of
>> about 88. That's pretty big. Since you had the same number without the
>> receipt date (I think...) then it's the encounter_id that's not being
>> counted right.
>
> I don't think that's Steve's problem, though. It's certainly
> misestimating, but nonetheless the cost estimate for the seqscan is
> 1902749.83 versus 14819.81 for the bitmap scan; it should've picked
> the bitmap scan anyway.
>
> I tried to duplicate the problem here, without any success; I get
>
> Bitmap Heap Scan on detail_summary ds (cost=422.01..801.27 rows=100 width=4)
> Recheck Cond: (encounter_id = ANY ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
> Filter: (receipt >= '1998-12-30'::date)
> -> Bitmap Index Scan on detail_summary_encounter_id_idx (cost=0.00..421.98 rows=100 width=0)
> Index Cond: (encounter_id = ANY ('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
>
> so either this has been fixed by a post-8.2.3 bug fix (which I doubt,
> it doesn't seem familiar at all) or there's some additional contributing
> factor. Steve, are you using any nondefault planner parameters?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 23:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:28 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-12 23:29 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> Here's my planner parameters:
I copied all these, and my 8.2.x still likes the bitmap scan a lot
better than the seqscan. Furthermore, I double-checked the CVS history
and there definitely haven't been any changes in that area in REL8_2
branch since 8.2.3. So I'm a bit baffled. Maybe the misbehavior is
platform-specific ... what are you on exactly? Is there anything
nonstandard about your Postgres installation?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:29 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 23:28 ` Steve <cheetah@tanabi.org>
0 siblings, 0 replies; 29+ messages in thread
From: Steve @ 2007-04-12 23:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
It's a redhat enterprise machine running AMD x64 processors.
Linux ers3.dddcorp.com 2.6.9-42.0.10.ELsmp #1 SMP Fri Feb 16 17:13:42 EST
2007 x86_64 x86_64 x86_64 GNU/Linux
It was compiled by me, straight up, nothing weird at all, no odd compiler
options or wahtever :)
So yeah :/ I'm quite baffled as well,
Talk to you later,
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> Here's my planner parameters:
>
> I copied all these, and my 8.2.x still likes the bitmap scan a lot
> better than the seqscan. Furthermore, I double-checked the CVS history
> and there definitely haven't been any changes in that area in REL8_2
> branch since 8.2.3. So I'm a bit baffled. Maybe the misbehavior is
> platform-specific ... what are you on exactly? Is there anything
> nonstandard about your Postgres installation?
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-12 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:42 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
1 sibling, 2 replies; 29+ messages in thread
From: Tom Lane @ 2007-04-12 23:32 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> ... even if I force it to use the indexes
> (enable_seqscan=off) it doesn't make it any faster really :/
Does that change the plan, or do you still get a seqscan?
BTW, how big is this table really (how many rows)?
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 23:40 ` Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 29+ messages in thread
From: Steve @ 2007-04-12 23:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Table size: 16,037,728 rows
With enable_seqscan=off I get:
Bitmap Heap Scan on detail_summary ds (cost=4211395.20..4213045.32
rows=1099 width=4)
Recheck Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
-> Bitmap Index Scan on detail_summary_receipt_encounter_idx
(cost=0.00..4211395.17 rows=1099 width=0)
Index Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id =
ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
The explain analyze is pending, running it now (however it doens't really
appear to be any faster using this plan).
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> ... even if I force it to use the indexes
>> (enable_seqscan=off) it doesn't make it any faster really :/
>
> Does that change the plan, or do you still get a seqscan?
>
> BTW, how big is this table really (how many rows)?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-13 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-13 00:00 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> With enable_seqscan=off I get:
> -> Bitmap Index Scan on detail_summary_receipt_encounter_idx
> (cost=0.00..4211395.17 rows=1099 width=0)
> Index Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id =
> ANY ...
> The explain analyze is pending, running it now (however it doens't really
> appear to be any faster using this plan).
Yeah, that index is nearly useless for this query --- since the receipt
condition isn't really eliminating anything, it'll have to look at every
index entry :-( ... in fact, do so again for each of the IN arms :-( :-(
So it's definitely right not to want to use that plan. Question is, why
is it seemingly failing to consider the "right" index?
I'm busy setting up my test case on an x86_64 machine right now, but
I rather fear it'll still work just fine for me. Have you got any
nondefault parameter settings besides the ones you already mentioned?
Another thing that might be interesting, if you haven't got a problem
with exclusive-locking the table for a little bit, is
BEGIN;
DROP INDEX each index except detail_summary_encounter_id_idx
EXPLAIN the problem query
ROLLBACK;
just to see if it does the right thing when it's not distracted by
all the "wrong" indexes (yeah, I'm grasping at straws here). If you
set up the above as a SQL script it should only take a second to run.
Please try this with both settings of enable_seqscan --- you don't need
to do "explain analyze" though, we just want to know which plan it picks
and what the cost estimate is.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-13 00:05 ` Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Steve @ 2007-04-13 00:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
If the other indexes are removed, with enable_seqscan=on:
Bitmap Heap Scan on detail_summary ds (cost=154.10..1804.22 rows=1099
width=4)
Recheck Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
Filter: (receipt >= '1998-12-30'::date)
-> Bitmap Index Scan on detail_summary_encounter_id_idx
(cost=0.00..154.07 rows=1099 width=0)
Index Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
With it off:
Bitmap Heap Scan on detail_summary ds (cost=154.10..1804.22 rows=1099
width=4)
Recheck Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
Filter: (receipt >= '1998-12-30'::date)
-> Bitmap Index Scan on detail_summary_encounter_id_idx
(cost=0.00..154.07 rows=1099 width=0)
Index Cond: (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[]))
Either way, it runs perfectly fast. So it looks like the indexes are
confusing this query like you suspected. Any advise? This isn't the only
query we run on this table, much as I'd absolutely love to kill off some
indexes to imrpove our nightly load times I can't foul up the other
queries :)
Thank you very much for all your help on this issue, too!
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> With enable_seqscan=off I get:
>
>> -> Bitmap Index Scan on detail_summary_receipt_encounter_idx
>> (cost=0.00..4211395.17 rows=1099 width=0)
>> Index Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id =
>> ANY ...
>
>> The explain analyze is pending, running it now (however it doens't really
>> appear to be any faster using this plan).
>
> Yeah, that index is nearly useless for this query --- since the receipt
> condition isn't really eliminating anything, it'll have to look at every
> index entry :-( ... in fact, do so again for each of the IN arms :-( :-(
> So it's definitely right not to want to use that plan. Question is, why
> is it seemingly failing to consider the "right" index?
>
> I'm busy setting up my test case on an x86_64 machine right now, but
> I rather fear it'll still work just fine for me. Have you got any
> nondefault parameter settings besides the ones you already mentioned?
>
> Another thing that might be interesting, if you haven't got a problem
> with exclusive-locking the table for a little bit, is
>
> BEGIN;
> DROP INDEX each index except detail_summary_encounter_id_idx
> EXPLAIN the problem query
> ROLLBACK;
>
> just to see if it does the right thing when it's not distracted by
> all the "wrong" indexes (yeah, I'm grasping at straws here). If you
> set up the above as a SQL script it should only take a second to run.
> Please try this with both settings of enable_seqscan --- you don't need
> to do "explain analyze" though, we just want to know which plan it picks
> and what the cost estimate is.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-13 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
0 siblings, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-13 00:20 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> Either way, it runs perfectly fast. So it looks like the indexes are
> confusing this query like you suspected. Any advise?
Wow --- sometimes grasping at straws pays off. I was testing here with
just a subset of the indexes to save build time, but I bet that one of
the "irrelevant" ones is affecting this somehow. Time to re-test.
If you have some time to kill, it might be interesting to vary that
begin/rollback test script to leave one or two other indexes in place,
and see if you can identify exactly which other index(es) get it
confused.
I'm about to go out to dinner with the wife, but will have a closer
look when I get back, or tomorrow morning. We'll figure this out.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-13 00:34 ` Steve <cheetah@tanabi.org>
2007-04-13 02:40 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 29+ messages in thread
From: Steve @ 2007-04-13 00:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Okay -- I started leaving indexes on one by one.
The explain broke when the detail_summary_receipt_encounter_idx index was
left on (receipt, encounter_id).
Just dropping that index had no effect, but there's a LOT of indexes that
refer to receipt. So on a hunch I tried dropping all indexes that refer
to receipt date and that worked -- so it's the indexes that contain
receipt date that are teh problem.
For more fun, I tried leaving the index that's just receipt date alone
(index detail_summary_receipt_id_idx) and THAT produced the correct query;
it's all these multi-column queries that are fouling things up, it would
seem!
.... So does this mean I should experiment with dropping those indexes?
I'm not sure if that will result in 'bad things' as there are other
complicated actions like sorts that may go real slow if I drop those
indexes. BUT I think it'll be easy to convince the customer to drop their
absurdly complicated sorts if I can come back with serious results like
what we've worked out here.
And thanks again -- have a good dinner! :)
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> Either way, it runs perfectly fast. So it looks like the indexes are
>> confusing this query like you suspected. Any advise?
>
> Wow --- sometimes grasping at straws pays off. I was testing here with
> just a subset of the indexes to save build time, but I bet that one of
> the "irrelevant" ones is affecting this somehow. Time to re-test.
>
> If you have some time to kill, it might be interesting to vary that
> begin/rollback test script to leave one or two other indexes in place,
> and see if you can identify exactly which other index(es) get it
> confused.
>
> I'm about to go out to dinner with the wife, but will have a closer
> look when I get back, or tomorrow morning. We'll figure this out.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-13 02:40 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 29+ messages in thread
From: Tom Lane @ 2007-04-13 02:40 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> Okay -- I started leaving indexes on one by one.
> ...
> .... So does this mean I should experiment with dropping those indexes?
No, I think this means there's a planner bug to fix. I haven't quite
scoped out what it is yet, though.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
@ 2007-04-13 03:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 03:55 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 04:38 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 29+ messages in thread
From: Tom Lane @ 2007-04-13 03:49 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> Just dropping that index had no effect, but there's a LOT of indexes that
> refer to receipt. So on a hunch I tried dropping all indexes that refer
> to receipt date and that worked -- so it's the indexes that contain
> receipt date that are teh problem.
I'm still not having any luck reproducing the failure here. Grasping at
straws again, I wonder if it's got something to do with the order in
which the planner examines the indexes --- which is OID order. Could
you send me the results of
select indexrelid::regclass from pg_index where indrelid = 'detail_summary'::regclass order by indexrelid;
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-13 03:55 ` Steve <cheetah@tanabi.org>
1 sibling, 0 replies; 29+ messages in thread
From: Steve @ 2007-04-13 03:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Here you go:
detail_summary_b_record_status_idx
detail_summary_batch_id_idx
detail_summary_batchnum_idx
detail_summary_carrier_id_idx
detail_summary_duplicate_id_idx
detail_summary_e_record_status_idx
detail_summary_encounter_id_idx
detail_summary_encounternum_idx
detail_summary_export_date_idx
detail_summary_hedis_date_idx
detail_summary_member_name_idx
detail_summary_member_num_idx
detail_summary_p_record_status_idx
detail_summary_patient_control_idx
detail_summary_procedurecode_idx
detail_summary_product_line_idx
detail_summary_provider_id_idx
detail_summary_raps_date_idx
detail_summary_receipt_id_idx
detail_summary_referrering_prov_id_idx
detail_summary_rendering_prov_id_idx
detail_summary_rendering_prov_name_idx
detail_summary_servicedate_idx
ds_sort_1
ds_sort_10
ed_cbee_norev
ed_cbee_norev_p
ed_cbee_rev
ed_cbee_rev_p
mcbe
mcbe_p
mcbe_rev
mcbe_rev_p
mcbee_norev
mcbee_norev_p
mcbee_rev
mcbee_rev_p
pcbee_norev
pcbee_norev_p
pcbee_rev
pcbee_rev_p
rcbee_norev
rcbee_norev_p
rp_cbee_norev
rp_cbee_norev_p
rp_cbee_rev
rp_cbee_rev_p
sd_cbee_norev
sd_cbee_norev_p
sd_cbee_rev
sd_cbee_rev_p
testrev
testrev_p
detail_summary_receipt_encounter_idx
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> Just dropping that index had no effect, but there's a LOT of indexes that
>> refer to receipt. So on a hunch I tried dropping all indexes that refer
>> to receipt date and that worked -- so it's the indexes that contain
>> receipt date that are teh problem.
>
> I'm still not having any luck reproducing the failure here. Grasping at
> straws again, I wonder if it's got something to do with the order in
> which the planner examines the indexes --- which is OID order. Could
> you send me the results of
>
> select indexrelid::regclass from pg_index where indrelid = 'detail_summary'::regclass order by indexrelid;
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-13 04:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-17 03:24 ` Fwd: Strangely Variable Query Performance Robins Tharakan <robins@pobox.com>
1 sibling, 1 reply; 29+ messages in thread
From: Tom Lane @ 2007-04-13 04:38 UTC (permalink / raw)
To: Steve <cheetah@tanabi.org>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Steve <cheetah@tanabi.org> writes:
> On Thu, 12 Apr 2007, Tom Lane wrote:
>> I'm still not having any luck reproducing the failure here. Grasping at
>> straws again, I wonder if it's got something to do with the order in
>> which the planner examines the indexes --- which is OID order. Could
>> you send me the results of
> Here you go:
Nope, still doesn't fail for me. [ baffled and annoyed... ] There must
be something about your situation that's relevant but we aren't
recognizing it. Are you in a position to let me ssh into your machine
and try to debug it? Or other options like sending me a dump of your
database? I'm about out of steam for tonight, but let's talk about it
off-list tomorrow.
regards, tom lane
^ permalink raw reply [nested|flat] 29+ messages in thread
* Fwd: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 04:38 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-17 03:24 ` Robins Tharakan <robins@pobox.com>
2007-04-17 03:43 ` Fwd: Strangely Variable Query Performance Robins <tharakan@gmail.com>
0 siblings, 1 reply; 29+ messages in thread
From: Robins Tharakan @ 2007-04-17 03:24 UTC (permalink / raw)
To: pgsql-performance
Hi Tom / Steve,
Am one of the silent readers of performance issues that come up on this list
(and are discussed in detail) ... just like this one.
If and when you do come up with a solution, please do post some details
about them here... (i say that coz it seems that for obvious reasons, things
must have gone off air after tom's last email, and one can understand that).
But an analysis, or atleast a pointer may be of help to someone (like me)
reading this list.
Thanks
Robins
---------- Forwarded message ----------
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Apr 13, 2007 10:08 AM
Subject: Re: [PERFORM] Strangely Variable Query Performance
To: Steve <cheetah@tanabi.org>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>, PostgreSQL Performance <
pgsql-performance@postgresql.org>
Steve <cheetah@tanabi.org> writes:
> On Thu, 12 Apr 2007, Tom Lane wrote:
>> I'm still not having any luck reproducing the failure here. Grasping at
>> straws again, I wonder if it's got something to do with the order in
>> which the planner examines the indexes --- which is OID order. Could
>> you send me the results of
> Here you go:
Nope, still doesn't fail for me. [ baffled and annoyed... ] There must
be something about your situation that's relevant but we aren't
recognizing it. Are you in a position to let me ssh into your machine
and try to debug it? Or other options like sending me a dump of your
database? I'm about out of steam for tonight, but let's talk about it
off-list tomorrow.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Robins
^ permalink raw reply [nested|flat] 29+ messages in thread
* Fwd: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 04:38 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-17 03:24 ` Fwd: Strangely Variable Query Performance Robins Tharakan <robins@pobox.com>
@ 2007-04-17 03:43 ` Robins <tharakan@gmail.com>
2007-04-17 04:44 ` Re: Fwd: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 29+ messages in thread
From: Robins @ 2007-04-17 03:43 UTC (permalink / raw)
To: pgsql-performance
Hi Tom / Steve,
Am one of the silent readers of performance issues that come up on this list
(and are discussed in detail) ... just like this one.
If and when you do come up with a solution, please do post some details
about them here... (i say that coz it seems that for obvious reasons, things
must have gone off air after tom's last email, and one can understand that).
But an analysis, or atleast a pointer may be of help to someone (like me)
reading this list.
Thanks
Robins
---------- Forwarded message ----------
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Apr 13, 2007 10:08 AM
Subject: Re: [PERFORM] Strangely Variable Query Performance
To: Steve <cheetah@tanabi.org>
Cc: Scott Marlowe <smarlowe@g2switchworks.com >, PostgreSQL Performance <
pgsql-performance@postgresql.org>
Steve <cheetah@tanabi.org> writes:
> On Thu, 12 Apr 2007, Tom Lane wrote:
>> I'm still not having any luck reproducing the failure here. Grasping at
>> straws again, I wonder if it's got something to do with the order in
>> which the planner examines the indexes --- which is OID order. Could
>> you send me the results of
> Here you go:
Nope, still doesn't fail for me. [ baffled and annoyed... ] There must
be something about your situation that's relevant but we aren't
recognizing it. Are you in a position to let me ssh into your machine
and try to debug it? Or other options like sending me a dump of your
database? I'm about out of steam for tonight, but let's talk about it
off-list tomorrow.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Fwd: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-13 03:49 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 04:38 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-17 03:24 ` Fwd: Strangely Variable Query Performance Robins Tharakan <robins@pobox.com>
2007-04-17 03:43 ` Fwd: Strangely Variable Query Performance Robins <tharakan@gmail.com>
@ 2007-04-17 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 29+ messages in thread
From: Tom Lane @ 2007-04-17 04:44 UTC (permalink / raw)
To: robins@pobox.com; +Cc: pgsql-performance
Robins <tharakan@gmail.com> writes:
> If and when you do come up with a solution, please do post some details
> about them here... (i say that coz it seems that for obvious reasons, things
> must have gone off air after tom's last email, and one can understand that).
> But an analysis, or atleast a pointer may be of help to someone (like me)
> reading this list.
Oh, sorry, the subsequent discussion moved over to pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00621.php
and -patches:
http://archives.postgresql.org/pgsql-patches/2007-04/msg00374.php
Those are good places to look if a discussion on -bugs or other lists
seems to tail off...
regards, tom lane
PS: the reason I couldn't reproduce the behavior was just that the dummy
data I was using didn't have the right statistics.
^ permalink raw reply [nested|flat] 29+ messages in thread
* Re: Strangely Variable Query Performance
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Re: Strangely Variable Query Performance Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 23:00 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Re: Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Re: Strangely Variable Query Performance Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-12 23:42 ` Steve <cheetah@tanabi.org>
1 sibling, 0 replies; 29+ messages in thread
From: Steve @ 2007-04-12 23:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; pgsql-performance
Here's the explain analyze with seqscan = off:
Bitmap Heap Scan on detail_summary ds (cost=4211395.20..4213045.32
rows=1099 width=4) (actual time=121288.825..121305.908 rows=112 loops=1)
Recheck Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id = ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
-> Bitmap Index Scan on detail_summary_receipt_encounter_idx
(cost=0.00..4211395.17 rows=1099 width=0) (actual
time=121256.681..121256.681 rows=112 loops=1)
Index Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id =
ANY
('{8813186,8813187,8813188,8813189,8813190,8813191,8813192,8813193,8813194,8813195,8813196,8813197,8813198,8813199,8813200,8813201,8813202,8813203,8813204,8813205,8813206,8813207,8813208,8813209,8813210,8813211,8813212,8813213,8813214,8813215,8813216,8813217,8813218,8813219,8813220,8813221,8813222,8813223,8813224,8813225,8813226,8813227,8813228,8813229,8813230,8813231,8813232,8813233,8813234,8813235,8813236,8813237,8813238,8813239,8813240,8813241,8813242,8813243,8813244,8813245,8813246,8813247,8813248,8813249,8813250,8813251,8813252,8813253,8813254,8813255,8813256,8813257,8813258,8813259,8813260,8813261,8813262,8813263,8813264,8813265,8813266,8813267,8813268,8813269,8813270,8813271,8813272,8813273,8813274,8813275,8813276,8813277,8813278,8813279,8813280,8813281,8813282,8813283,8813284,8815534}'::integer[])))
Total runtime: 121306.233 ms
Your other question is answered in the other mail along with the
non-analyze'd query plan :D
Steve
On Thu, 12 Apr 2007, Tom Lane wrote:
> Steve <cheetah@tanabi.org> writes:
>> ... even if I force it to use the indexes
>> (enable_seqscan=off) it doesn't make it any faster really :/
>
> Does that change the plan, or do you still get a seqscan?
>
> BTW, how big is this table really (how many rows)?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 29+ messages in thread
end of thread, other threads:[~2007-04-17 04:44 UTC | newest]
Thread overview: 29+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-12 21:03 Strangely Variable Query Performance Steve <cheetah@tanabi.org>
2007-04-12 21:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 21:40 ` Steve <cheetah@tanabi.org>
2007-04-12 22:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:03 ` Steve <cheetah@tanabi.org>
2007-04-12 22:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:24 ` Steve <cheetah@tanabi.org>
2007-04-12 21:52 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:04 ` Steve <cheetah@tanabi.org>
2007-04-12 22:14 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-04-12 22:41 ` Steve <cheetah@tanabi.org>
2007-04-12 23:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 22:56 ` Steve <cheetah@tanabi.org>
2007-04-12 23:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:28 ` Steve <cheetah@tanabi.org>
2007-04-12 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:40 ` Steve <cheetah@tanabi.org>
2007-04-13 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:05 ` Steve <cheetah@tanabi.org>
2007-04-13 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 00:34 ` Steve <cheetah@tanabi.org>
2007-04-13 02:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 03:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-13 03:55 ` Steve <cheetah@tanabi.org>
2007-04-13 04:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-17 03:24 ` Robins Tharakan <robins@pobox.com>
2007-04-17 03:43 ` Robins <tharakan@gmail.com>
2007-04-17 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-12 23:42 ` Steve <cheetah@tanabi.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox