From: Tom Lane <tgl@sss.pgh.pa.us>
To: Oliveiros Cristina <oliveiros.cristina@marktest.pt>
Cc: pgsql-sql@postgresql.org
Subject: Re: performance question
Date: Fri, 08 May 2009 13:55:36 -0400
Message-ID: <10868.1241805336@sss.pgh.pa.us> (raw)
In-Reply-To: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt>
References: <005801c9cff2$c5424660$ec5a3d0a@marktestcr.marktest.pt>
"Oliveiros Cristina" <oliveiros.cristina@marktest.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, oliveiros.cristina@marktest.pt
Subject: Re: performance question
In-Reply-To: <10868.1241805336@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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