Re: Matching indexe for timestamp

On 1/9/17, Job <Job(at)colliniconsulting(dot)it> wrote:
> Hello,
>
> on this table:
>
> Table "public.gruorari_tmp"
> Column | Type |
> Modifiers
> -----------+------------------------+--------------------------------------------------------------------------
> id | numeric(1000,1) | not null default
> function_get_next_sequence('gruorari_tmp_id_seq'::text)
> idgrucate | numeric(1000,1) |
> dalle | time without time zone |
> alle | time without time zone |
> gg_sett | integer |
> azione | character varying |
> Indexes:
> "keygruorari_tmp" PRIMARY KEY, btree (id)
> "gruorari_tmp_alle_idx" btree (alle)
> "gruorari_tmp_dalle_alle_idx" btree (dalle, alle)
> "gruorari_tmp_dalle_idx" btree (dalle)
> "gruorari_tmp_gg_sett_idx" btree (gg_sett)
> "gruorari_tmp_idgrucate_idx" btree (idgrucate)
>
> i have a specific condition (i report example value):
> "and ( gruorari_tmp.id is null or ( 2 = gg_sett and '16:00:00'::time between
> gruorari_tmp.dalle and gruorari_tmp.alle ) )"
>
> But in the query planner, at that point, Postgresql 9.6.1 seems not to use
> any index (single on dalle / alle field and combindex index on dalle+alle)

Of course. There is no reason to use any index because the condition
"gruorari_tmp.id is null" is not covered by any of them. To find such
rows you have to scan all table (because there can be with any
"alle".."dalle" values), that's why Postgres uses SeqScan.

> but it use seqscan:
>
> Seq Scan on gruorari_tmp (cost=0.00..5.90 rows=290 width=68) (actual
> time=0.014..0.062 rows=290 loops=1)
> -> Hash (cost=164.06..164.06 rows=1
> width=29) (actual time=0.770..0.770 rows=1 loops=1)
> And it seems to be the main cost for the whole query.

> Which kind of index should i use for that condition/fields?

for _big_ tables Postgres can use "bitmap OR" node if there are two
indexes which can be used.
So create index for "gruorari_tmp.id is null" and gin/gist "(gg_sett,
timerange(gruorari_tmp.dalle, gruorari_tmp.alle))" (see below).

If you use a condition like "<value> between colA and colB", some sort
of a "timerange" is the best case. Unfortunately there is no such
type, but it is easy to create it by an example[1].
Then you can use btree_gin or btree_gist (depending on a base index
type) extension to use an ordinary type column(s) with range type
column(s).

P.S.: Postgres can not to use indexes even if they are right because
according to a statistics SeqScan will take similar access time.

[1]https://www.postgresql.org/docs/9.6/static/rangetypes.html#RANGETYPES-DEFINING

--
Best regards,
Vitaly Burovoy