Btw - I tried playing around with some of the other planner cost
constants but I wasn't able to get the planner to choose the index
scan. It seems like the issue is that the estimated cost for
fetching one row from the index (3.02) is a little high in my
case. Is there any way that I can adjust t
Thanks Tom,
That modifies the query plan slightly, but the planner still decides to
do a hash join for the lte_user table aliased 't'. Though, if I
make this change and set enable_hashjoin to off, the query plan (and
execution time) gets even better.
enable_hashjoin = on
Meetesh Karia <[EMAIL PROTECTED]> writes:
> Sure. The lte_user table is just a collection of users. user_id is assigned=
> uniquely using a sequence. During some processing, we create a candidates=
> table (candidates617004 in our case). This table is usually a temp table.=
> sourceid is a user_id
Thanks John. I've answered your questions below:
Has lte_user and candidates617004 been recently ANALYZEd? All estimates,except for the expected number of rows from lte_user seem to be okay.
I ANALYZEd both tables just before putting together my first
email. And, unfortunately, modifying the stat
Are you referring to the statistics gathering target for ANALYZE?
Based on your email, I just tried the following and then re-ran the
explain analyze but got the same "incorrect" plan:
alter table candidates617004
alter column sourceId set statistics 1000,
alter column targetId set statis
Meetesh Karia wrote:
> Hi all,
>
> We're using 8.0.3 and we're seeing a problem where the planner is
> choosing a seq scan and hash join over an index scan. If I set
> enable_hashjoin to off, then I get the plan I'm expecting and the query
> runs a lot faster. I've also tried lowering the random
[Meetesh Karia - Tue at 12:19:27AM +0200]
> We're using 8.0.3 and we're seeing a problem where the planner is choosing a
> seq scan and hash join over an index scan. If I set enable_hashjoin to off,
> then I get the plan I'm expecting and the query runs a lot faster. I've also
> tried lowering t
Hi all,
We're using 8.0.3 and we're seeing a problem where the planner is
choosing a seq scan and hash join over an index scan. If I set
enable_hashjoin to off, then I get the plan I'm expecting and the query
runs a lot faster. I've also tried lowering the random page cost
(even to 1) but the pl
