PostgreSQL - How to decrease select statement excution time

Question

My Postgres version: "PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 32-bit"
The table which i am going to deal with; contains columns
1. eventtime - timestamp without timezone
2. serialnumber - character varying(32)
3. sourceid - integer

and 4 other columns

here is my select statement

SELECT eventtime, serialnumber
    FROM   t_el_eventlog
    WHERE
    eventtime at time zone 'CET'  >  CURRENT_DATE  and
    sourceid = '14';

the excution time for the above query is 59647ms

And in my r script i have 5 these kind of queries (excution time = 59647ms*5). Without using time zone 'CET', the excution time is very less - but in my case I must use time zone 'CET' and if I am right the high excution time is beacuse of these timezone.

my query plan

enter image description here

text query enter image description here

explain analyze query(without timezone) enter image description here

Is there anyway that I can decrease the query excution time for my select statement

assuming the eventtime is in utc, simply set the CURRENT_DATE to utc instead of eventtime to CET: SELECT [...] WHERE eventtime > CURRENT_DATE at time zone 'UTC' — byMike
Please show the text query plan, the output from EXPLAIN ANALYZE SELECT ... — Craig Ringer

byMike byMike · Accepted Answer · 2015-07-01T16:14:55

Since the distribution of the values is unknown to me, there is no clear way of solving the problem.

But one problem is obvious: There is an index for the eventtime column, but since the query operates with a function over that column, the index can't be used.

eventtime in time zone 'UTC' > CURRENT_DATE

Either the index has to be dropped and recreated with that function or the query has to be rewritten.

Recreate the index (example):

CREATE INDEX ON t_el_eventlog (timezone('UTC'::text, eventtime));

(this is the same as eventtime in time zone 'UTC')

This matches the filter with the function, the index can be used.

I suspect the sourceid not having a great distribution, not having very much different values. In that case, dropping the index on sourceid AND dropping the index on eventtime with creating a new index over eventtime and sourceid could be an idea:

CREATE INDEX ON t_el_eventlog (timezone('UTC'::text, eventtime), sourceid);

This is what the theory is telling us. I made a few tests around that, with a table with around 10 Million Rows, eventtime distribution within 36 hours and only 20 different sourceids (1..20). Distribution is very random. The best results were in an index over eventtime, sourceid (no function index) and adjusting the query.

CREATE INDEX ON t_el_eventlog (eventtime, sourceid);
-- make sure there is no index on source id. we need to force postgres to this index.

-- make sure, postgres learns about our index
ANALYZE; VACUUM;

-- use timezone function on current date (guessing timezone is CET)
SELECT * FROM t_el_eventlog
 WHERE eventtime > timezone('CET',CURRENT_DATE) AND sourceid = 14;

With the table having 10'000'000 rows, this query returns me about 500'000 rows in only 400ms. (instead of about 1400 up to 1700 in all other combinations).

Finding the best match between the indexes and the query is the quest. I suggest some research, a recommendation is http://use-the-index-luke.com

this is what the query plan looks like with the last approach:

Index Only Scan using evlog_eventtime_sourceid_idx on evlog  (cost=0.45..218195.13 rows=424534 width=0)
     Index Cond: ((eventtime > timezone('CET'::text, (('now'::cstring)::date)::timestamp with time zone)) AND (sourceid = 14))

as you can see, this is a perfect match...

PostgreSQL - How to decrease select statement excution time

1 Answers