Re: Matching indexe for timestamp

On 01/09/2017 01:58 PM, Job 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) 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?

As before, giving just snippets of the information is going to prolong
or prevent arriving at an answer. So:

1) What is the complete query?

2) What is the complete EXPLAIN ANALYZE?

>
> Thank you!
>
> /F

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com