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