explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tnTH : http://dba.stackexchange.com/questions/108394/how-to-optimize-the-sort-in-postgres-query

Settings
# exclusive inclusive rows x rows loops node
1. 0.045 615.333 ↑ 1.0 20 1

Limit (cost=7,180.01..7,181.66 rows=20 width=621) (actual time=615.232..615.333 rows=20 loops=1)

2. 0.126 615.288 ↑ 542.4 40 1

Unique (cost=7,178.36..8,968.36 rows=21,697 width=621) (actual time=615.129..615.288 rows=40 loops=1)

3. 330.670 615.162 ↑ 504.6 43 1

Sort (cost=7,178.36..7,232.60 rows=21,697 width=621) (actual time=615.125..615.162 rows=43 loops=1)

  • Sort Key: ((power((((139.745069 - to_number(tms.lng, '000D00000000'::text)) / 0.0111) * 1000000::numeric), 2::numeric) + power((((35.662978 - to_number(tms.lat, '000D00000000'::text)) / 0.0091) * 1000000::numeric), 2::numeric))), tms.id, tms.store_cd, tms.store_nm_org, tms.store_nm_chg, tms.store_nm_kn_org, tms.store_nm_kn_chg, tms.address1_org, tms.address2_org, tms.address3_org, tms.address1_chg, tms.address2_chg, tms.address3_chg, tms.search_address, tms.tel, tms.kenku_cd, tms.tensyu_cd, tms.new_flg, tms.lat, tms.lng, tms.point, tms.level, tms.view, tms.search_word, tms.create_datetime, tms.update_datetime, tms.delete_datetime, tms.proc_flg, tms.type, tms.latlng_chg_flg, tms.view_chg_flg
  • Sort Method: quicksort Memory: 22864kB
4. 253.234 284.492 ↓ 1.2 25,391 1

Hash Join (cost=334.39..5,615.61 rows=21,697 width=621) (actual time=3.891..284.492 rows=25,391 loops=1)

  • Hash Cond: (tms.tensyu_cd = mt.small_cd)
5. 27.892 31.098 ↓ 1.0 23,141 1

Bitmap Heap Scan on ttb_members_store tms (cost=331.97..4,657.03 rows=22,804 width=621) (actual time=3.693..31.098 rows=23,141 loops=1)

  • Recheck Cond: ((view <> 0) AND (type = ANY ('{2,3}'::integer[])) AND (delete_datetime IS NULL))
6. 3.206 3.206 ↓ 1.0 23,141 1

Bitmap Index Scan on ttb_members_store_idx3 (cost=0.00..326.27 rows=22,804 width=0) (actual time=3.206..3.206 rows=23,141 loops=1)

7. 0.082 0.160 ↑ 1.0 63 1

Hash (cost=1.63..1.63 rows=63 width=3) (actual time=0.160..0.160 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
8. 0.078 0.078 ↑ 1.0 63 1

Seq Scan on mtb_tensyu mt (cost=0.00..1.63 rows=63 width=3) (actual time=0.005..0.078 rows=63 loops=1)

Total runtime : 616.526 ms