On Tue, Nov 28, 2017 at 10:55 PM, Emanuel Alvarez <ema@xxxxxxxxxxxxxxxxxx> 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
