sql - Add an index to a timestamp with time zone -

-

i want improve slow query, thinking add index, don't know index type better case.

select count(*) ct events dtt @ time zone 'america/santiago'    >= date(now() @ time zone 'america/santiago') + interval '1s'

query plan:

"aggregate  (cost=128032.03..128032.04 rows=1 width=0) (actual time=3929.083..3929.083 rows=1 loops=1)" "  ->  seq scan on events  (cost=0.00..125937.68 rows=837742 width=0) (actual time=113.080..3926.972 rows=25849 loops=1)" "        filter: (timezone('america/santiago'::text, dtt) >= (date(timezone('america/santiago'::text, now())) + '00:00:01'::interval))" "        rows removed filter: 2487386" "planning time: 0.179 ms" "execution time: 3929.136 ms"
  • the query gets count of events of day.
  • dtt timestamp time zone column.
  • i'm using postgresql 9.4.

note: erwin advices query run little faster still think isn't fast enough. 

"aggregate  (cost=119667.76..119667.77 rows=1 width=0) (actual time=3687.151..3687.152 rows=1 loops=1)" "  ->  seq scan on vehicle_events  (cost=0.00..119667.14 rows=250 width=0) (actual time=104.635..3687.068 rows=469 loops=1)" "        filter: (dtt >= timezone('america/santiago'::text, date_trunc('day'::text, timezone('america/santiago'::text, now()))))" "        rows removed filter: 2513337" "planning time: 0.164 ms" "execution time: 3687.204 ms"

first, fix query make predicate sargable:

select count(*) ct   events  dtt >= date_trunc('day', now() @ time zone 'america/santiago')                                       @ time zone 'america/santiago'

use column value , move calculations parameter.

that's right, after deriving local start of day, apply at time zone second time convert timestamp timestamptz again. details:

explanation step-by-step

  1. now()
    .. postgres implementation sql standard current_timestamp. both 100 % equivalent, can use either. returns current point in time timestamptz - display of value takes time zone of current session consideration, that's irrelevant value.

  2. now() at time zone 'america/santiago'
    .. computes local time given time zone. resulting data type timestamp. allow for:

  3. date_trunc( now() @ time zone 'america/santiago' )
    .. truncates time component local start of day in 'america/santiago', independent of current time zone setting.

  4. date_trunc('day', now() @ time zone 'america/santiago') at time zone 'america/santiago'
    .. feeding timestamp at time zone construct corresponding timestamptz value (utc internally) compare timestamptz value dtt to.

i removed + interval '1s', suspecting have been abusing convert date timestamp. use date_trunc() instead produce timestamp value.

now, plain (default) btree index on dtt do. of course, index used, if predicate selective enough.

create index events_dtt_idx on events (dtt);

if important queries consider recent rows, partial index might more. details:


Comments

Post a Comment

Popular posts from this blog

php - Admin SDK -- get information about the group -

-
please, help. https://www.googleapis.com/admin/directory/v1/groups/106274944935548788283 sending request obtain information group id 1062 ... here link method in google https://developers.google.com/admin-sdk/directory/v1/guides/manage-groups#get_group in response error - 401 unauthorized. oauth authenticate via user has received rights necessary request, included in console admin sdk. authorization successful, there token. not can not understand. if not hard - response. requested information: file_get_contents("https://www.googleapis.com/admin/directory/v1/groups/1062749‌​44935548788283?key=aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4"); id group - "106274944935548788283" key applications in case - "aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4" error: failed open stream: http request failed! http/1.0 401 unauthorized
Read more

Python Error - TypeError: input expected at most 1 arguments, got 3 -

-
can explain why can not use your_name in goal variable? my_name = "bryson" my_age = 29 your_name = input ("what name? ") your_age = input ("what age? ") print ("my name is", my_name,", , am", my_age, "years old.") print ("your name is", your_name,", , are", your_age,".") print("thank buying book,", your_name,"!") goal = input ("what favorite part of book,", your_name, "?") print("awesome!") the error is: goal = input ("what favorite part of book,", your_name, "?") typeerror: input expected @ 1 arguments, got 3 you got error because in fact gave 3 arguments input function when expecting 1 (namely, string prompt). in input ("what favorite part of book,", your_name, "?") ---------------------------------------- , ---------, --- the underlined parts comma-separated arguments: st...
Read more

python - Pygame screen.blit not working -

-
in program creating in python/pygame, screen.blit not working. in other programs have made, screen.blit works. error: traceback (most recent call last): file "c:...\main.py", line 46, in <module> screen.blit(sight.image, sight.rect) attributeerror: 'int' object has no attribute 'blit' the error in question caused screen variable being allocated integer instead of pygame.surface() object.perhaps accidentally rewrote variable somewhere? from can make of doing, seems stating position of blit full rect instead of coordinate. when do: screen.blit(sight.image,sight.rect) what program seeing is: screen.blit(sight.image,(sight.rect.x,sight.rect.y,sight.rect.width,sight.rect.height)) i assuming don't intend this. remember rect object consists of position, width , height. to correct code, put (sight.rect.x,sight.rect.y) location goes instead this: screen.blit(sight.image,(sight.rect.x,sight.rect.y)) the .x , .y corres...
Read more