How to see index was rejected for seq scan?

From: Corey Taylor <corey(dot)taylor(dot)fl(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to see index was rejected for seq scan?
Date: 2017-12-16 09:03:04
Message-ID: CADBz386iXZ=Jgs_N-3u4G7UtCt3A-x3EMFzKJ1DE-j-d8NGz+g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Steele 2017-12-16 12:19:59 Re: PgBackRest question?
Previous Message chiru r 2017-12-15 21:36:16 Re: PgBackRest question?