Re: seq vs index scan in join query

On Tue, Nov 28, 2017 at 10:55 PM, Emanuel Alvarez <ema(at)abductedcow(dot)com(dot)ar>
wrote:

> hi all,
>
> we're in the process of optimizing some queries and we've noted a case
> where the planner prefers a sequential scan instead of using an index,
> while the index scan is actually much faster. to give you some
> context: we have two main tables, keywords and results. keywords has
> approximately 700.000 rows; while results holds approximately one row
> per keyword per day (roughly 70m at the moment, not all keywords are
> active at any given day). results is currently partitioned by
> (creation) time. it's also worth noting that we use SSDs in our
> servers, and have random_page_cost set to 1.
>
>
> the problematic query looks like this:
>
> SELECT keywords.strategy_id, results.position, results.created_at FROM
> results
> JOIN keywords ON results.keyword_id = keywords.id
> WHERE results.account_id = 1
> AND results.created_at >= '2017-10-25 00:00:00.000000'
> AND results.created_at <= '2017-11-10 23:59:59.999999';
>
>
> as you can see in the query plan [1] a sequential scan is preferred.
>

I would say the preference is not for the seq scan, but rather for the hash
join. If the seq scan couldn't be fed into a hash join, it would not look
very favorable.

I think hash joins are a bit optimistic on how much cpu time they think
they use building the hash table. You can probably get better plans for
this type of query by increasing cpu_tuple_cost to 0.02 or 0.03. That
works because the hash join over the seq scan has to scan 700,000 tuples to
build the hash table, which is then probed only 70,000 time, while the
nested loop index scan just probes the 70,000 rows is needs directly and
ignores the other 90%.

...

>
> on the other hand, if we disable sequential scans (SET enable_seqscan
> = 0), we see than not only the query runs faster but the cost seems to
> be lower, as seen in the query plan [2].
>

The costs for plan 2 doesn't look lower to me. 196754.90 > 120421.32

>
> in this example the gain it's not much: ~0.5s. but when we add a
> second join table with additional keyword data the planner still
> prefers a sequential scan on a table that has +6m rows. query looks
> like this:
>
> SELECT keywords.strategy_id, results.position, results.created_at,
> keyword_data.volume FROM results
> JOIN keywords ON results.keyword_id = keywords.id
> JOIN keyword_data ON keywords.keyword_data_id = keyword_data.id
> WHERE results.account_id = 1
> AND results.created_at >= '2017-10-25 00:00:00.000000'
> AND results.created_at <= '2017-11-19 23:59:59.999999';
>
>
> in this case query takes up to 8s, query plan can be found in [3].
> obviously dataset has to be large to prefer a sequential on a 6m rows
> table. similarly, reducing the created_at range or using an account_id
> with fewer keywords makes the planner prefer index scan, accelerating
> the query considerably.
>

If that is the query you are really concerned about, we would have to see
the faster plan for that query. (Or better yet, keep the created_at range
the same, and set enable_hashjoin to off to get it to switch plans).

This looks like is a very skewed query. keyword_data has 10 rows for every
row in keywords, yet adding a join to keyword_data doesn't increase the
number of rows returned by the query at all. That is weird, isn't it?

For what its worth, in my hands on your simpler query it likes to sort the
70,000 qualifying rows from "results" table, then do a merge join againsts
the index on keywords. And it truly is the faster option. I have to
enable_mergejoin=off before I can get either of your plans. Once I do, the
nested loop does seem to be faster than the hash join but not by the two
fold that you see, and they jump around quite a bit from run to run.

Cheers,

Jeff