Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to see index was rejected for seq scan?

How to see index was rejected for seq scan?

From
Corey Taylor
Date:
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).


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.

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)))

Re: How to see index was rejected for seq scan?

From
Peter Eisentraut
Date:
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

Re: How to see index was rejected for seq scan?

From
Corey Taylor
Date:
> 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.