How to see index was rejected for seq scan?
This isn't meant to be a question about improving a slow query or
determining that the planner was wrong.
It seems like a simple and obvious answer, but I would love to know if
there is any documentation you can point me to read on this.
Essentially, if an index was deemed not to save cost during the input scan,
the planner will schedule a seq scan. What I'm wondering if there is
anything that indicates a valid index for the scan was found and rejected
(reason doesn't necessarily matter). I couldn't find anything in the using
explain document, but I have probably missed it. I would assume the index
would be used to determine if the filter requires a large scan count.
I didn't want to send an email with a bunch of unnecessary SQL, but I
created a sqlfiddle with the example if one is required although any
theoretical scenario where an index is used or rejected would work (unless
that's wrong).
http://sqlfiddle.com/#!17/8782b/25
This is an execution plan for a seq scan due to a large number of rows for
a datetime range. If you reduce the datetime range enough (1 week to 1 day
in the data sample in the sqlfiddle), it switches to index scan.
https://explain.depesz.com/s/AurP
Seq Scan
<http://www.depesz.com/2013/04/27/explaining-the-unexplainable-part-2/#seq-scan>
on data (cost=0.00..62.67 rows=503 width=19) (actual rows= loops=)
- Filter: ((datetime <= now()) AND (datetime >= (now() - '7
days'::interval)))
On 12/16/17 04:03, Corey Taylor wrote:
Essentially, if an index was deemed not to save cost during the input
scan, the planner will schedule a seq scan. What I'm wondering if there
is anything that indicates a valid index for the scan was found and
rejected (reason doesn't necessarily matter). I couldn't find anything
in the using explain document, but I have probably missed it. I would
assume the index would be used to determine if the filter requires a
large scan count.
This isn't really possible in the general case, because the planner
doesn't actually materialize all possible plans for complex queries,
which would be very expensive.
But for simple queries, you might get some insight if you set
enable_seqscan to off. Then the planner will give you an index-using
plan if it is at all possible. Then you can compare the costs. If the
planner still gives you a sequential scan, then the index was not
applicable for other reasons.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
But for simple queries, you might get some insight if you set
enable_seqscan to off. Then the planner will give you an index-using
plan if it is at all possible. Then you can compare the costs. If the
planner still gives you a sequential scan, then the index was not
applicable for other reasons.
Thanks! This gives me enough information. I was essentially looking for
something concrete to discuss for cases where someone even more novice than
myself in query optimization points to issues after adding an index.
I have a tangential question that I am curious about more than it being
helpful in building queries.
Is index information used to determine the input scan cost or is that
determine through another mechanism or other metadata in the table? I mean
this for a simple case and not in an exhaustive general sense. For example
a table with an id and timestamp column with an index on the id and
timestamp filtered by a date range.