postgresql - 如何在 postgresql 中索引复杂查询

Question

我想在 postgresql 中为这个查询建立一个索引

此查询的当前运行时间超过 500 毫秒

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

我的表中有超过 500K 条记录

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"

有索引

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"

Answer 1

使用 PostgreSQL 的 EXPLAIN 功能将有助于显示查询使用的路径。这将执行 SQL，因此请为此做好准备。

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