Question slow query

På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B <
patrickbakerbr@gmail.com <mailto: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 <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general