Thread: [GENERAL] Question slow query

[GENERAL] Question slow query

From
Patrick B
Date:
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:
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
 
Attachment

Re: [GENERAL] Question slow query

From
Patrick B
Date:


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:

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:
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:
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