0

I have a local setup of the geocoder, after some test, I realized that the time for the states is different. Some states have more data and the geocoding takes much more for these. E.G. for MA the average time is around 300ms but for CA the time is around 20-30 seconds. I have this on a Ubuntu machine with 8GB of ram and HDD.

My idea is to create a different DB in SSD to put the states with more data and keep the smaller ones in a HDD, is this a feasible approach to improve performance?

1

Yes. You don't have to have them in a different db either if you don't want to. You can store individual tables in different table spaces and table spaces map to any place you want on any disk.

I'm puzzled though that your CA is taking 20-30 seconds. I have CA and about 20 other states loaded and CA doesn't take that long. Your stats may be out of date or you are missing indexes, so you might want to run this covered in step 9 of the manual - http://postgis.net/docs/manual-2.3/postgis_installation.html#install_tiger_geocoder_extension

SELECT install_missing_indexes();
vacuum analyze verbose tiger.addr;
vacuum analyze verbose tiger.edges;
vacuum analyze verbose tiger.faces;
vacuum analyze verbose tiger.featnames;
vacuum analyze verbose tiger.place;
vacuum analyze verbose tiger.cousub;
vacuum analyze verbose tiger.county;
vacuum analyze verbose tiger.state;
vacuum analyze verbose tiger.zip_lookup_base;
vacuum analyze verbose tiger.zip_state;
vacuum analyze verbose tiger.zip_state_loc;

Regarding putting tables in different table spaces, first you need to create a table space on your ssd.

1) Create a folder to house the table space and make sure the postgres process has full permission (preferably owns it)

2)

CREATE TABLESPACE tiger_big  LOCATION '/path/to/folder';

If you are on windows, You do something like 'D:/foldertohouse' for the path.

Next you can move tables to that table space. For example to move the CA ones would be

3)

ALTER TABLE tiger_data.ca_addr SET TABLESPACE tiger_big;
ALTER TABLE tiger_data.ca_edges SET TABLESPACE tiger_big;
ALTER TABLE tiger_data.ca_faces SET TABLESPACE tiger_big;
ALTER TABLE tiger_data.ca_featnames SET TABLESPACE tiger_big;
ALTER TABLE tiger_data.ca_place SET TABLESPACE tiger_big; 

There are some others for CA, but you get the idea

  • How much time it takes the geocoding for CA on your machine? does it has HDD? I think that CA data might not fit in ram and that's why it takes that long. Does it makes sense? – Luis Ramon Ramirez Rodriguez Jul 4 '17 at 13:48
  • 1
    depends on address but usually under 200 ms. I have ssd drives and about 10GB motherboard shared mem dedicated t postgres – LR1234567 Jul 4 '17 at 23:34

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.