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
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 - 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 the
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 page
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 statistics
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
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 (in
