I think I understand now after reading the notes here on the merge join
algorithm:
https://github.com/postgres/postgres/blob/4ea51cdfe85ceef8afabceb03c446574daa0ac23/src/backend/executor/nodeMergejoin.c
The index scanning node doesn't know the max id of the vehicle table and so
can't know when t
Thanks Tom, that sounds like what is happening. Some additional
comments/questions inline.
Tom Lane-2 wrote
> I think what must be happening is that the planner notes the maximum
> possible value of v.id and supposes that the mergejoin will stop far short
> of completion because v.id spans just a
I wrote:
> [ assorted possible workarounds ]
Actually, an easy fix might be to create a 2-column index on
usagestats(type, tid). I think the planner should be able to
use that to produce sorted output for the mergejoin, and you'd
get the best of both worlds, because the indexscan will stop
immedi
Jake Magner writes:
> I am having problems with a join where the planner picks a merge join and an
> index scan on one of the tables. Manually disabling merge joins and running
> the query both ways shows the merge join takes over 10 seconds while a hash
> join takes less than 100ms. The planner t
random_page_cost = 4
seq_page_cost = 1
Regardless of the the choice to use the index scan and random access to the
rows, how come in the second query with the freq > -1 condition, it accesses
far fewer pages with the same index scan even though no rows are filtered
out?
Thanks
--
View this mes
Hi
what is your random_page_cost and seq_page_cost?
Regards
Pavel Stehule
2015-03-19 7:23 GMT+01:00 Jake Magner :
> I am having problems with a join where the planner picks a merge join and
> an
> index scan on one of the tables. Manually disabling merge joins and running
> the query both ways
