How to see index was rejected for seq scan?

Поиск
Список
Период
Сортировка
От Corey Taylor
Тема How to see index was rejected for seq scan?
Дата
Msg-id CADBz386iXZ=Jgs_N-3u4G7UtCt3A-x3EMFzKJ1DE-j-d8NGz+g@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to see index was rejected for seq scan?  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
Список 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).


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

В списке pgsql-general по дате отправления:

Предыдущее
От: chiru r
Дата:
Сообщение: Re: PgBackRest question?
Следующее
От: David Steele
Дата:
Сообщение: Re: PgBackRest question?