From: | Venkata B Nagothi <nag1010(at)gmail(dot)com> |
---|---|
To: | Patrick B <patrickbakerbr(at)gmail(dot)com> |
Cc: | Andreas Joseph Krogh <andreas(at)visena(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question slow query |
Date: | 2017-01-27 10:32:34 |
Message-ID: | CAEyp7J9m0TtU79h79iw+kzTMKQOoCeOfBtO=uwW_Y2n2DcfK2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Jan 17, 2017 at 6:27 AM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
>
>
> 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));
>
>
What about the SQL Query ? what does the column stats show ? The EXPLAIN
output is the complete output ?
Regards,
Venkata B N
Database Consultant
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2017-01-27 11:01:33 | Re: Question slow query |
Previous Message | Venkata B Nagothi | 2017-01-27 10:26:51 | Re: Transaction apply speed on the standby |