2

I've been trying to optimize the sort in the following query. I ran EXPLAIN ANALYZE and the majority of the time is during the sort when it arranges the output by distance.

I've tried converting the fields for lat, lng into text and removed the to_number function to see if it would make a difference but the results didn't change.

I tried creating an index in ttb_members_store on lat, lng to see if that would help and the queries were about 125 ms slower.

What would be the next step?

SELECT tms.*, 
       0 as kyori ,
       (power(
           (139.745069 - to_number(tms.lng,'000D00000000')) / 0.0111 * 1000000, 
           2) + 
        power(
           (35.662978 - to_number(tms.lat,'000D00000000')) / 0.0091   *1000000, 
           2)
       ) AS kyori2 
FROM ttb_members tms, 
     mtb_tenshu mt 
WHERE (tms.tenshu_cd = mt.small_cd) 
  AND (tms.view <> 0) 
  AND (tms.type in (2,3)) 
  AND (tms.delete_datetime is null)  
ORDER BY kyori2 ASC 
OFFSET 20 LIMIT 20;

EXPLAIN ANALYZE output:

---------------------------------------------------------------------------
 Limit  (cost=7180.01..7181.66 rows=20 width=621) (actual time=615.232..615.333 rows=20 loops=1)
   ->  Unique  (cost=7178.36..8968.36 rows=21697 width=621) (actual time=615.129..615.288 rows=40 loops=1)
         ->  Sort  (cost=7178.36..7232.60 rows=21697 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::num
eric) + 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, t
ms.new_flg, tms.lat, tms.lng, tms.point, tms.level, tms.view, tms.search_word, tms.create_datetime, tms.update_datetime, tms.del
ete_datetime, tms.proc_flg, tms.type, tms.latlng_chg_flg, tms.view_chg_flg

               Sort Method: quicksort  Memory: 22864kB
               ->  Hash Join  (cost=334.39..5615.61 rows=21697 width=621) (actual time=3.891..284.492 rows=25391 loops=1)
                     Hash Cond: (tms.tensyu_cd = mt.small_cd)
                     ->  Bitmap Heap Scan on ttb_members_store tms  (cost=331.97..4657.03 rows=22804 width=621) (actual time=3.6
93..31.098 rows=23141 loops=1)
                           Recheck Cond: ((view <> 0) AND (type = ANY ('{2,3}'::integer[])) AND (delete_datetime IS NULL))
                           ->  Bitmap Index Scan on ttb_members_store_idx3  (cost=0.00..326.27 rows=22804 width=0) (actual time=
3.206..3.206 rows=23141 loops=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
                           ->  Seq Scan on mtb_tensyu mt  (cost=0.00..1.63 rows=63 width=3) (actual time=0.005..0.078 rows=63 lo
ops=1)
 Total runtime: 616.526 ms

Here is the definition of the two tables:

Table ttb_members_store

     Column      |            Type             |     Modifiers
-----------------+-----------------------------+-----------------------------
 id              | integer                     | not null default nextval(...
 store_cd        | character(9)                | not null
 store_nm_org    | text                        |
 store_nm_chg    | text                        |
 store_nm_kn_org | text                        |
 store_nm_kn_chg | text                        |
 address1_org    | text                        |
 address2_org    | text                        |
 address3_org    | text                        |
 address1_chg    | text                        |
 address2_chg    | text                        |
 address3_chg    | text                        |
 search_address  | text                        |
 tel             | text                        |
 kenku_cd        | character(4)                |
 tensyu_cd       | character(2)                |
 new_flg         | smallint                    |
 lat             | text                        |
 lng             | text                        |
 point           | text                        |
 level           | smallint                    |
 view            | smallint                    |
 search_word     | text                        |
 create_datetime | timestamp without time zone | not null
 update_datetime | timestamp without time zone |
 delete_datetime | timestamp without time zone |
 proc_flg        | smallint                    |
 type            | smallint                    |
 latlng_chg_flg  | smallint                    |
 view_chg_flg    | smallint                    |
Indexes:
    "ttb_members_store_pkey" PRIMARY KEY, btree (id)
    "ttb_members_store_idx1" UNIQUE, btree (store_cd)

Table mtb_tensyu

     Column      |            Type             |     Modifiers
-----------------+-----------------------------+-----------------------------
 id              | integer                     | not null default nextval(...
 large_cd        | integer                     | not null
 large_nm        | text                        | not null
 small_cd        | character(2)                | not null
 small_nm        | text                        | not null
 create_datetime | timestamp without time zone | not null default now()
 update_datetime | timestamp without time zone |
 delete_datetime | timestamp without time zone |
Indexes:
    "mtb_tensyu_pkey" PRIMARY KEY, btree (id)
    "mtb_tensyu_idx1" btree (small_cd)
  • I've put the output of my explain analyze at explain.depesz.com. While that gives me a nice graphic breakdown of the results I posted, it doesn't really tell me what I need to fix. It just tells me what's broken (albeit, in a more attractive fashion). – Tensigh Jul 29 '15 at 9:50
  • Which of your WHERE conditions are constant, and which can change (in what range)? Why do you join to mtb_tenshu (at all)? Please provide a table definition of ttb_members (\d ttb_members in psql) and the definition of ttb_members_store_idx3. Generally, consider instructions in the tag info for [postgresql-performance]. – Erwin Brandstetter Jul 29 '15 at 13:54
  • All of the "AND" statements are constant. The join between mtb_tenshu and ttb_members_store is that tenshu has the type of store that will be displayed. I will post the table definitions and idx when I get to work later today. – Tensigh Jul 29 '15 at 23:56
  • The table ttb_members_store lists the stores, their addresses and phone numbers, lat and long, etc. mtb_tenshu lists what type of store they are. The tenshu_cd in both tables helps match what type of store to the address, so if someone is looking for a Chinese restaurant in a certain area, it will be displayed. – Tensigh Jul 30 '15 at 2:13
  • The EXPLAIN output mentions ttb_members_store_idx3, which is missing in your description. – Erwin Brandstetter Jul 30 '15 at 3:30
1

lat and lng are obviously numbers, so you should store them as appropriate numeric data type, not as text. That makes for smaller storage and disallows invalid input, it simplifies your query syntax and is also a bit faster overall. It's not going to do much for your query, though.
For instance:

lat             | numeric
lng             | numeric

So mtb_tenshu has store types. But if we can assume referential integrity and no row is excluded by the join, the table still is just noise in the presented query since you don't use it at all.

So we have this simplified query:

SELECT tm.* -- return only columns that are needed!
       0 as kyori
     , ((139.745069 - lng) / 0.0111 * 1000000)^2
     + (( 35.662978 - lat) / 0.0091 * 1000000)^2 AS kyori2 
FROM   ttb_members tm
WHERE  view <> 0
AND    type IN (2,3)
AND    delete_datetime IS NULL
ORDER  BY kyori2
OFFSET 20
LIMIT  20;

And since you commented:

All of the "AND" statements are constant.

You can support this very efficiently with a partial, functional index:

CREATE INDEX ttb_members_foo_idx ON ttb_members
 ((((139.745069 - lng) / 0.0111 * 1000000)^2
 + (( 35.662978 - lat) / 0.0091 * 1000000)^2));
WHERE  view <> 0
AND    type IN (2,3)
AND    delete_datetime IS NULL;

The expression and the WHERE conditions must be matches in the query to use this index.

Now Postgres can just skip the first 20 index entries and fetch the next 20 readily sorted in an index scan. Should be very fast.

|improve this answer|||||
  • There are 2 data listed here that are actually variables: 139.745069 and 35.662978 aren't constants. They're populated from the user's position. I grabbed this query as an example out of the logs. How could I recreate this index without using those data? – Tensigh Jul 30 '15 at 6:51
  • @Tensigh: If those are input parameters, that's a game changer. Consider a "nearest neighbor" search. I don't have time for a new answer I am afraid. Example with PostGis: dba.stackexchange.com/a/60711/3684. Plain Postgres: stackoverflow.com/a/22675324/939860 – Erwin Brandstetter Jul 30 '15 at 7:08
  • Thanks for the info, I'll look those up. I'll mark your answer. – Tensigh Jul 30 '15 at 8:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.