Thread: [GENERAL] Question slow query

[GENERAL] Question slow query

From

Patrick B

Date:

12 January 2017, 08:15:59

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?

Thanks!

Re: [GENERAL] Question slow query

From

Andreas Joseph Krogh

Date:

12 January 2017, 09:48:21

På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B <patrickbakerbr@gmail.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

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Attachment

msg-1553745-3.png

Re: [GENERAL] Question slow query

From

Patrick B

Date:

17 January 2017, 01:27:13

2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh <andreas@visena.com>:

På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B <patrickbakerbr@gmail.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

Re: [GENERAL] Question slow query

From

Venkata B Nagothi

Date:

27 January 2017, 16:32:34

On Tue, Jan 17, 2017 at 6:27 AM, Patrick B <patrickbakerbr@gmail.com> wrote:

2017-01-12 16:48 GMT+13:00 Andreas Joseph Krogh <andreas@visena.com>:
På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B <patrickbakerbr@gmail.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

Re: [GENERAL] Question slow query

From

John R Pierce

Date:

27 January 2017, 17:01:33

On 1/16/2017 11:27 AM, Patrick B wrote:

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

why did you prefix your index with timezone('etc/UTC'::text ??? that doesn't make sense to me at all. is timezone() some function you've defined? I'm not aware of any built in function by that name.

create index concurrently on table(columnname); should index a timestamp column, the same as any other column.

you still haven't told us what query it is thats taking 25 seconds.

-- 
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Question slow query

From

Tom Lane

Date:

27 January 2017, 19:44:52

John R Pierce <pierce@hogranch.com> writes:
> On 1/16/2017 11:27 AM, Patrick B wrote:
>> CREATE INDEX CONCURRENTLY ON note (timezone('Etc/UTC'::text, d_date));

> why did you prefix your index with timezone('etc/UTC'::text ??? that
> doesn't make sense to me at all.   is timezone() some function you've
> defined?  I'm not aware of any built in function by that name.

That's the internal spelling of "d_date AT TIME ZONE 'Etc/UTC'".

It's still a pretty good question why index that and not just d_date.

            regards, tom lane