StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow can I optimize this postgresql query?
text
Body
copied!<p>Below is a postgres query that seems to be taking far longer than I would expect. The field_instances table is indexed on both form_instance_id and field_id, and the form_instances table is indexed on workflow_state. So I thought it would be a fast query, but it takes forever. Can anybody help me interpret the query plan and what kinds of indexes to add to speed it up? Thanks.</p> <pre><code>explain analyze select form_id,form_instance_id,answer,field_id from form_instances,field_instances where workflow_state = 'DRqueued' and form_instance_id = form_instances.id and field_id = 'Book_EstimatedDueDate'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=8733.85..95692.90 rows=9277 width=29) (actual time=2550.000..15430.000 rows=11431 loops=1) Hash Cond: (field_instances.form_instance_id = form_instances.id) -> Bitmap Heap Scan on field_instances (cost=2681.11..89071.72 rows=47567 width=25) (actual time=850.000..13690.000 rows=51726 loops=1) Recheck Cond: ((field_id)::text = 'Book_EstimatedDueDate'::text) -> Bitmap Index Scan on index_field_instances_on_field_id (cost=0.00..2669.22 rows=47567 width=0) (actual time=830.000..830.000 rows=51729 loops=1) Index Cond: ((field_id)::text = 'Book_EstimatedDueDate'::text) -> Hash (cost=5911.34..5911.34 rows=11312 width=8) (actual time=1590.000..1590.000 rows=11431 loops=1) -> Bitmap Heap Scan on form_instances (cost=511.94..5911.34 rows=11312 width=8) (actual time=720.000..1570.000 rows=11431 loops=1) Recheck Cond: ((workflow_state)::text = 'DRqueued'::text) -> Bitmap Index Scan on index_form_instances_on_workflow_state (cost=0.00..509.11 rows=11312 width=0) (actual time=650.000..650.000 rows=11509 loops=1) Index Cond: ((workflow_state)::text = 'DRqueued'::text) Total runtime: 15430.000 ms (12 rows) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload