Matching indexe for timestamp

From: Job <Job(at)colliniconsulting(dot)it>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Matching indexe for timestamp
Date: 2017-01-09 21:58:11
Message-ID: 88EF58F000EC4B4684700C2AA3A73D7A08054EACC29C@W2008DC01.ColliniConsulting.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Thank you!

/F

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2017-01-09 22:01:23 Re: checkpoint clarifications needed
Previous Message Adrian Klaver 2017-01-09 21:24:24 Re: checkpoint clarifications needed