Re: Question slow query

2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B <
> patrickbakerbr(at)gmail(dot)com>:
>
> Hi guys,
>
> I've got a slow query, running at 25 seconds.
>
>
> -> Bitmap Heap Scan on ja_notes r_1103088 (cost=234300.55..1254978.62 rows=553275 width=101) (actual time=1423.411..10572.549 rows=475646 loops=1)
> Recheck Cond: (n_type = ANY ('{note,note_1,note_2}'::n_type[]))
> Filter: (timezone('Etc/UTC'::text, d_modified) >= '2016-08-07 23:12:34'::timestamp without time zone)
> Rows Removed by Filter: 13725231
>
>
>
> As you can see, 13.725.231 rows were removed by Filter..
>
> Should I create an index for this column?
>
>> d_modified | timestamp with time zone | default
>> statement_timestamp()
>
> I tried but it didn't help... the query doesn't even hit the index.
>
>
>
> CREATE INDEX q ON test USING BTREE (d_modified);
>
>
>
> Am I missing something?
>
>
> Yes, you've not shown us:
> 1. The query
> 2. The schema
> 3. Complete EXPLAIN-output
>
>

Sorry about that.. just wanted to understand how to index a timestamp
column. I was able to do this way:

>
> CREATE INDEX CONCURRENTLY ON note (timezone('Etc/UTC'::text, d_date));

Cheers
Patrick