Re: performance question

"Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> writes:
> Frankly, I cannot understand the reason for this, it seems the planner is taking radically diferent plans on the two conditions (they are below).

Yup, and you seem to be near the crossover point where it thinks they
have equal cost. You need to be fixing the inaccurate cost estimates.
The most obvious problem is the bad rowcount estimate here:

> " -> Bitmap Index Scan on "index" (cost=0.00..108.43 rows=3515 width=0) (actual time=14.466..14.466 rows=11 loops=17)"
> " Index Cond: (a."IDSiteResume" = c."IDResume")"

Perhaps increasing the statistics targets for one or both tables would
help on that.

Another odd thing is that essentially identical indexscans are taking
radically different times:

> " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=82 width=28) (actual time=881.146..2711.303 rows=23 loops=1)"
> " Index Cond: ("IDHitsAccount" = 378284)"
> " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-04'::date))"

> " -> Index Scan using "fki_FGK_SITERESUME_ACCOUNT" on t_sitesresumebydate a (cost=0.00..46644.30 rows=55 width=28) (actual time=5.825..23.828 rows=17 loops=1)"
> " Index Cond: ("IDHitsAccount" = 378284)"
> " Filter: (("dtDate" >= '2009-02-01'::date) AND ("dtDate" <= '2009-02-03'::date))"

I think probably the second one was fast because the data is already
cached, so you're not making an entirely fair comparison. If your
expectation is that the database is going to be operating under mostly
cached conditions, then you probably ought to adjust the planner cost
parameters to reflect that (look at effective_cache_size, and try
reducing random_page_cost).

regards, tom lane