explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ucsK5

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 8,517.147 ↓ 0.0 0 1

Sort (cost=272,198.53..272,198.54 rows=3 width=730) (actual time=8,517.147..8,517.147 rows=0 loops=1)

  • Sort Key: sso.created_at DESC
  • Sort Method: quicksort Memory: 25kB
2.          

CTE myconstants

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=160) (actual time=0.001..0.001 rows=1 loops=1)

4. 0.000 8,517.121 ↓ 0.0 0 1

Nested Loop (cost=153,743.87..272,198.47 rows=3 width=730) (actual time=8,517.121..8,517.121 rows=0 loops=1)

5. 0.002 8,517.121 ↓ 0.0 0 1

Nested Loop (cost=153,743.72..272,197.85 rows=3 width=1,064) (actual time=8,517.121..8,517.121 rows=0 loops=1)

6. 3,834.017 8,517.119 ↓ 0.0 0 1

Nested Loop (cost=153,743.30..272,193.73 rows=3 width=1,030) (actual time=8,517.119..8,517.119 rows=0 loops=1)

  • Join Filter: (((((((COALESCE(ssoa.first_name, ''::character varying))::text || ' '::text) || (COALESCE(ssoa.middle_name, ''::character varying))::text) || (COALESCE(ssoa.last_name, ''::character varying))::text) % ((myconstants.var1 || ' '::text) || myconstants.var2)) AND ((((((COALESCE(ssoa.address1, ''::character varying))::text || ' '::text) || (COALESCE(ssoa.address2, ''::character varying))::text) || ' '::text) || (COALESCE(ssoa.address3, ''::character varying))::text) % myconstants.var3) AND ((((COALESCE(ssoa.zip_code, ''::character varying))::text || ' '::text) || (COALESCE(ssoa.city, ''::character varying))::text) % myconstants.var4)) OR ((sso.email)::text = myconstants.var5))
  • Rows Removed by Join Filter: 478610
7. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on myconstants (cost=0.00..0.02 rows=1 width=160) (actual time=0.002..0.003 rows=1 loops=1)

8. 302.761 4,683.099 ↑ 1.0 478,610 1

Merge Join (cost=153,743.30..249,459.73 rows=478,610 width=870) (actual time=2,689.707..4,683.099 rows=478,610 loops=1)

  • Merge Cond: (ssoa.id_sales_order_address = sso.fk_sales_order_address_shipping)
9. 1,167.330 1,167.330 ↑ 1.0 957,220 1

Index Scan using spy_sales_order_address_pkey on spy_sales_order_address ssoa (cost=0.42..84,948.20 rows=957,220 width=786) (actual time=0.005..1,167.330 rows=957,220 loops=1)

10. 302.063 3,213.008 ↑ 1.0 478,610 1

Materialize (cost=153,742.84..156,135.89 rows=478,610 width=92) (actual time=2,689.692..3,213.008 rows=478,610 loops=1)

11. 724.123 2,910.945 ↑ 1.0 478,610 1

Sort (cost=153,742.84..154,939.37 rows=478,610 width=92) (actual time=2,689.687..2,910.945 rows=478,610 loops=1)

  • Sort Key: sso.fk_sales_order_address_shipping
  • Sort Method: external merge Disk: 48696kB
12. 2,186.822 2,186.822 ↑ 1.0 478,610 1

Seq Scan on spy_sales_order sso (cost=0.00..84,051.10 rows=478,610 width=92) (actual time=0.082..2,186.822 rows=478,610 loops=1)

13. 0.000 0.000 ↓ 0.0 0

Index Scan using spy_sales_order_item_fk_sales_order_index on spy_sales_order_item ssoi (cost=0.42..1.36 rows=1 width=38) (never executed)

  • Index Cond: (fk_sales_order = sso.id_sales_order)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using spy_oms_order_item_state_pkey on spy_oms_order_item_state soois (cost=0.14..0.16 rows=1 width=28) (never executed)

  • Index Cond: (id_oms_order_item_state = ssoi.fk_oms_order_item_state)
Planning time : 7.557 ms
Execution time : 8,541.790 ms