Skip to content

Instantly share code, notes, and snippets.

@osiro

osiro/query_performance.txt

Created April 7, 2015 09:00
Show Gist options
  • Save osiro/39380deec9d6483c2fcc to your computer and use it in GitHub Desktop.
Save osiro/39380deec9d6483c2fcc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query_performance.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_items_on_owner_id on items (cost=0.14..200000003297.04 rows=9 width=46)
Index Cond: (owner_id = 2)
Filter: ((((reports_count < 3) OR (owner_id = 2)) AND (NOT (SubPlan 1))) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
SubPlan 1
-> Result (cost=0.29..21.56 rows=50 width=0)
One-Time Filter: ((items.id = items.id) AND (items.owner_id <> 2))
-> Nested Loop (cost=0.29..21.56 rows=50 width=0)
-> Index Only Scan using index_reported_items_on_reported_by_id_and_item_id on reported_items (cost=0.14..8.16 rows=1 width=0)
Index Cond: ((reported_by_id = 2) AND (item_id = items.id))
-> Index Only Scan using index_items_on_reports_count on items reported_items_items (cost=0.14..12.89 rows=50 width=0)
SubPlan 2
-> Nested Loop (cost=20000000308.97..20000000321.91 rows=1 width=0)
Join Filter: (trades_1.id = trades.id)
-> HashAggregate (cost=20000000300.57..20000000300.69 rows=12 width=46)
Group Key: items_1.id, items_1.type, items_1.created_at, items_1.updated_at, items_1.archive_reason_id, items_1.item_condition, trades_1.responder_id, trades_1.id
-> Append (cost=10000000086.90..20000000300.33 rows=12 width=46)
-> Hash Semi Join (cost=10000000086.90..10000000150.10 rows=6 width=46)
Hash Cond: (responder_trade_items.trade_offer_id = "ANY_subquery".id)
-> Nested Loop (cost=4.69..67.70 rows=11 width=54)
-> Nested Loop (cost=4.53..65.19 rows=11 width=50)
-> Nested Loop (cost=4.38..23.18 rows=11 width=42)
-> Index Scan using items_pkey on items items_1 (cost=0.14..8.16 rows=1 width=38)
Index Cond: (id = items.id)
-> Bitmap Heap Scan on responder_trade_items (cost=4.24..14.91 rows=11 width=8)
Recheck Cond: (item_id = items.id)
-> Bitmap Index Scan on index_responder_trade_items_on_item_id (cost=0.00..4.24 rows=11 width=0)
Index Cond: (item_id = items.id)
-> Index Scan using trade_offers_pkey on trade_offers (cost=0.15..3.81 rows=1 width=8)
Index Cond: (id = responder_trade_items.trade_offer_id)
-> Index Scan using trades_pkey on trades trades_1 (cost=0.15..0.22 rows=1 width=8)
Index Cond: (id = trade_offers.trade_id)
-> Hash (cost=10000000079.72..10000000079.72 rows=200 width=4)
-> Subquery Scan on "ANY_subquery" (cost=10000000072.52..10000000079.72 rows=200 width=4)
-> Unique (cost=10000000072.52..10000000077.72 rows=200 width=16)
-> Sort (cost=10000000072.52..10000000075.12 rows=1040 width=16)
Sort Key: trade_offers_1.trade_id, trade_offers_1.created_at
-> Seq Scan on trade_offers trade_offers_1 (cost=10000000000.00..10000000020.40 rows=1040 width=16)
-> Hash Semi Join (cost=10000000086.90..10000000150.10 rows=6 width=46)
Hash Cond: (initiator_trade_items.trade_offer_id = "ANY_subquery_1".id)
-> Nested Loop (cost=4.69..67.70 rows=11 width=54)
-> Nested Loop (cost=4.53..65.19 rows=11 width=50)
-> Nested Loop (cost=4.38..23.18 rows=11 width=42)
-> Index Scan using items_pkey on items items_2 (cost=0.14..8.16 rows=1 width=38)
Index Cond: (id = items.id)
-> Bitmap Heap Scan on initiator_trade_items (cost=4.24..14.91 rows=11 width=8)
Recheck Cond: (item_id = items.id)
-> Bitmap Index Scan on index_initiator_trade_items_on_item_id (cost=0.00..4.24 rows=11 width=0)
Index Cond: (item_id = items.id)
-> Index Scan using trade_offers_pkey on trade_offers trade_offers_2 (cost=0.15..3.81 rows=1 width=8)
Index Cond: (id = initiator_trade_items.trade_offer_id)
-> Index Scan using trades_pkey on trades trades_2 (cost=0.15..0.22 rows=1 width=8)
Index Cond: (id = trade_offers_2.trade_id)
-> Hash (cost=10000000079.72..10000000079.72 rows=200 width=4)
-> Subquery Scan on "ANY_subquery_1" (cost=10000000072.52..10000000079.72 rows=200 width=4)
-> Unique (cost=10000000072.52..10000000077.72 rows=200 width=16)
-> Sort (cost=10000000072.52..10000000075.12 rows=1040 width=16)
Sort Key: trade_offers_3.trade_id, trade_offers_3.created_at
-> Seq Scan on trade_offers trade_offers_3 (cost=10000000000.00..10000000020.40 rows=1040 width=16)
-> Materialize (cost=8.40..19.15 rows=11 width=4)
-> Bitmap Heap Scan on trades (cost=8.40..19.10 rows=11 width=4)
Recheck Cond: ((initiator_id = 2) OR (responder_id = 2))
-> BitmapOr (cost=8.40..8.40 rows=11 width=0)
-> Bitmap Index Scan on index_trades_on_initiator_id_and_responder_id (cost=0.00..4.20 rows=6 width=0)
Index Cond: (initiator_id = 2)
-> Bitmap Index Scan on index_trades_on_responder_id (cost=0.00..4.20 rows=6 width=0)
Index Cond: (responder_id = 2)
SubPlan 3
-> Hash Join (cost=20000000712.91..20000000725.69 rows=5 width=4)
Hash Cond: (trades_4.id = trades_3.id)
-> HashAggregate (cost=20000000693.68..20000000699.04 rows=536 width=46)
Group Key: items_3.id, items_3.type, items_3.created_at, items_3.updated_at, items_3.archive_reason_id, items_3.item_condition, trades_4.responder_id, trades_4.id
-> Append (cost=10000000240.19..20000000682.96 rows=536 width=46)
-> Hash Semi Join (cost=10000000240.19..10000000340.80 rows=268 width=46)
Hash Cond: (responder_trade_items_1.trade_offer_id = "ANY_subquery_2".id)
-> Hash Join (cost=157.98..250.18 rows=535 width=54)
Hash Cond: (responder_trade_items_1.trade_offer_id = trade_offers_4.id)
-> Nested Loop (cost=0.30..85.14 rows=535 width=42)
-> Index Scan using items_pkey on items items_3 (cost=0.14..12.89 rows=50 width=38)
-> Index Scan using index_responder_trade_items_on_item_id on responder_trade_items responder_trade_items_1 (cost=0.15..1.33 rows=11 width=8)
Index Cond: (item_id = items_3.id)
-> Hash (cost=144.68..144.68 rows=1040 width=12)
-> Merge Join (cost=0.30..144.68 rows=1040 width=12)
Merge Cond: (trade_offers_4.trade_id = trades_4.id)
-> Index Scan using index_trade_offers_on_trade_id_and_created_at on trade_offers trade_offers_4 (cost=0.15..63.75 rows=1040 width=8)
-> Index Scan using trades_pkey on trades trades_4 (cost=0.15..65.10 rows=1130 width=8)
-> Hash (cost=10000000079.72..10000000079.72 rows=200 width=4)
-> Subquery Scan on "ANY_subquery_2" (cost=10000000072.52..10000000079.72 rows=200 width=4)
-> Unique (cost=10000000072.52..10000000077.72 rows=200 width=16)
-> Sort (cost=10000000072.52..10000000075.12 rows=1040 width=16)
Sort Key: trade_offers_5.trade_id, trade_offers_5.created_at
-> Seq Scan on trade_offers trade_offers_5 (cost=10000000000.00..10000000020.40 rows=1040 width=16)
-> Hash Semi Join (cost=10000000240.19..10000000336.80 rows=268 width=46)
Hash Cond: (initiator_trade_items_1.trade_offer_id = "ANY_subquery_3".id)
-> Hash Join (cost=157.98..246.18 rows=535 width=54)
Hash Cond: (initiator_trade_items_1.trade_offer_id = trade_offers_6.id)
-> Nested Loop (cost=0.30..81.14 rows=535 width=42)
-> Index Scan using items_pkey on items items_4 (cost=0.14..12.89 rows=50 width=38)
-> Index Scan using index_initiator_trade_items_on_item_id on initiator_trade_items initiator_trade_items_1 (cost=0.15..1.26 rows=11 width=8)
Index Cond: (item_id = items_4.id)
-> Hash (cost=144.68..144.68 rows=1040 width=12)
-> Merge Join (cost=0.30..144.68 rows=1040 width=12)
Merge Cond: (trade_offers_6.trade_id = trades_5.id)
-> Index Scan using index_trade_offers_on_trade_id_and_created_at on trade_offers trade_offers_6 (cost=0.15..63.75 rows=1040 width=8)
-> Index Scan using trades_pkey on trades trades_5 (cost=0.15..65.10 rows=1130 width=8)
-> Hash (cost=10000000079.72..10000000079.72 rows=200 width=4)
-> Subquery Scan on "ANY_subquery_3" (cost=10000000072.52..10000000079.72 rows=200 width=4)
-> Unique (cost=10000000072.52..10000000077.72 rows=200 width=16)
-> Sort (cost=10000000072.52..10000000075.12 rows=1040 width=16)
Sort Key: trade_offers_7.trade_id, trade_offers_7.created_at
-> Seq Scan on trade_offers trade_offers_7 (cost=10000000000.00..10000000020.40 rows=1040 width=16)
-> Hash (cost=19.10..19.10 rows=11 width=4)
-> Bitmap Heap Scan on trades trades_3 (cost=8.40..19.10 rows=11 width=4)
Recheck Cond: ((initiator_id = 2) OR (responder_id = 2))
-> BitmapOr (cost=8.40..8.40 rows=11 width=0)
-> Bitmap Index Scan on index_trades_on_initiator_id_and_responder_id (cost=0.00..4.20 rows=6 width=0)
Index Cond: (initiator_id = 2)
-> Bitmap Index Scan on index_trades_on_responder_id (cost=0.00..4.20 rows=6 width=0)
Index Cond: (responder_id = 2)
SubPlan 4
-> Bitmap Heap Scan on users_favourites (cost=4.18..6.24 rows=1 width=0)
Recheck Cond: (item_id = items.id)
Filter: (user_id = 2)
-> Bitmap Index Scan on index_users_favourites_on_item_id (cost=0.00..4.17 rows=4 width=0)
Index Cond: (item_id = items.id)
SubPlan 5
-> Bitmap Heap Scan on users_favourites users_favourites_1 (cost=4.26..6.45 rows=15 width=4)
Recheck Cond: (user_id = 2)
-> Bitmap Index Scan on index_users_favourites_on_user_id_and_item_id (cost=0.00..4.26 rows=15 width=0)
Index Cond: (user_id = 2)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment