How to index complex queries in postgresql

Question

I want to build an index for this queries in postgresql

Current runtime for this query is more than 500ms

UPDATE `delayed_jobs` 
SET `delayed_jobs`.`locked_at` = '2015-03-12 11:44:02.000000',   
    `delayed_jobs`.`locked_by` = 'host:ip-172-31-21-148 pid:22442' 
WHERE ((run_at <= '2015-03-12 11:44:02.663471' AND (locked_at IS NULL OR  
     locked_at < '2015-03-12 07:44:02.663490') OR locked_by = 'host:ip-
     172-31-21-148 pid:22442') AND failed_at IS NULL) AND 
     `delayed_jobs`.`queue` = 'optimize_image' 
ORDER BY priority ASC, run_at ASC LIMIT 1

I have more than 500K records in the table

EXPLAIN ANALYZE select * from delayed_jobs WHERE run_at <= '2015-03-12 11:44:02.663471' AND locked_at IS NULL OR locked_at < '2015-03-12 07:44:02.663490' OR locked_by = 'host:ip-172-31-21-148 pid:22442' AND failed_at IS NULL AND delayed_jobs.queue = 'optimize_image' ORDER BY priority ASC, run_at ASC LIMIT 1

"Limit  (cost=49450.67..49450.68 rows=1 width=347) (actual time=315.763..315.763 rows=0 loops=1)"
"  ->  Sort  (cost=49450.67..49450.71 rows=15 width=347) (actual time=315.762..315.762 rows=0 loops=1)"
"        Sort Key: priority, run_at"
"        Sort Method: quicksort  Memory: 25kB"
"        ->  Seq Scan on delayed_jobs  (cost=0.00..49450.60 rows=15 width=347) (actual time=315.758..315.758 rows=0 loops=1)"
"              Filter: (((run_at <= '2015-03-12 11:44:02.663471'::timestamp without time zone) AND (locked_at IS NULL)) OR (locked_at < '2015-03-12 07:44:02.66349'::timestamp without time zone) OR (((locked_by)::text = 'host:ip-172-31-21-148 pid:22442'::tex (...)"
"Total runtime: 315.795 ms"

with index

create index idx_to_optimize 
   on delayed_jobs(run_at, locked_at, locked_by)
   where queue = 'optimize_image' and failed_at is null

"Limit  (cost=0.00..44283.87 rows=1 width=347) (actual time=3470.609..3470.609 rows=0 loops=1)"
"  ->  Index Scan using index_delayed_jobs_on_priority_and_run_at_and_locked_by on delayed_jobs  (cost=0.00..885677.39 rows=20 width=347) (actual time=3470.609..3470.609 rows=0 loops=1)"
"        Filter: (((run_at <= '2015-03-12 11:44:02.663471'::timestamp without time zone) AND (locked_at IS NULL)) OR (locked_at < '2015-03-12 07:44:02.66349'::timestamp without time zone) OR (((locked_by)::text = 'host:ip-172-31-21-148 pid:22442'::text) AND (...)"
"Total runtime: 614.652 ms"

make use of your explain analyze result in explain.depesz.com(A tool for finding a real cause for slow queries) — Vivek S.
– Vivek S., Commented Mar 13, 2015 at 6:30
UPDATE `delayed_jobs` set ... is invalid SQL for Postgres. Postgres does not allow the use of the non-standard dreaded backticks — user330315
– user330315, Commented Mar 13, 2015 at 8:09
@a_horse_with_no_name that was added by rails app.... the actual query was without backtickks — chirag7jain
– chirag7jain, Commented Mar 15, 2015 at 3:29

rockerBOO · Accepted Answer · 2015-03-13 04:38:27Z

2

Using the EXPLAIN feature of PostgreSQL will help to show the path the query uses. This will execute the SQL, so be prepared for this.

http://www.postgresql.org/docs/current/static/sql-explain.html

answered Mar 13, 2015 at 4:38

rockerBOO

3,0402 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thilo Over a year ago

EXPLAIN does not execute the SQL. EXPLAIN ANALYZE does.

Collectives™ on Stack Overflow

How to index complex queries in postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related