Vryjdfkk

I am working on Posrgres 9.6 with PostGIS 2.3, hosted on AWS RDS. I'm trying to optimize some geo-radius queries for data that comes from different tables.

I'm considering two approaches: single query with multiple joins or two separate but simpler queries.

At a high level, and simplifying the structure, my schema is:

CREATE EXTENSION "uuid-ossp";

CREATE EXTENSION IF NOT EXISTS postgis;





CREATE TABLE addresses (

    id bigint NOT NULL,

    latitude double precision,

    longitude double precision,

    line1 character varying NOT NULL,

    "position" geography(Point,4326),

    CONSTRAINT enforce_srid CHECK ((st_srid("position") = 4326))

);



CREATE INDEX index_addresses_on_position ON addresses USING gist ("position");



CREATE TABLE locations (

    id bigint NOT NULL,

    uuid uuid DEFAULT uuid_generate_v4() NOT NULL,

    address_id bigint NOT NULL

);



CREATE TABLE shops (

    id bigint NOT NULL,

    name character varying NOT NULL,

    location_id bigint NOT NULL

);



CREATE TABLE inventories (

    id bigint NOT NULL,

    shop_id bigint NOT NULL,

    status character varying NOT NULL

);

The addresses table holds the geographical data. The position column is calculated from the lat-lng columns when the rows are inserted or updated.

Each address is associated to one location.

Each address may have many shops, and each shop will have one inventory.

I've omitted them for brevity, but all the tables have the proper foreign key constraints and btree indexes on the reference columns.

The tables have a few hundreds of thousands of rows.

With that in place, my main use case can be satisfied by this single query, which searches for addresses within 1000 meters from a central geographical point (10.0, 10.0) and returns data from all the tables:

SELECT

    s.id AS shop_id,

    s.name AS shop_name,

    i.status AS inventory_status,

    l.uuid AS location_uuid,

    a.line1 AS addr_line,

    a.latitude AS lat,

    a.longitude AS lng

FROM addresses a

JOIN locations l ON l.address_id = a.id

JOIN shops s ON s.location_id = l.id

JOIN inventories i ON i.shop_id = s.id

WHERE ST_DWithin(

    a.position,                             -- the position of each address

    ST_SetSRID(ST_Point(10.0, 10.0), 4326), -- the center of the circle

    1000,                                   -- radius distance in meters

    true

);

This query works, and EXPLAIN ANALYZE shows that it does correctly use the GIST index.

However, I could also split this query in two and manage the intermediate results in the application layer. For example, this works too:

--- only search for the addresses

SELECT

    a.id as addr_id,

    a.line1 AS addr_line,

    a.latitude AS lat,

    a.longitude AS lng

FROM addresses a

WHERE ST_DWithin(

    a.position,                             -- the position of each address

    ST_SetSRID(ST_Point(10.0, 10.0), 4326), -- the center of the circle

    1000,                                   -- radius distance in meters

    true

);



--- get the rest of the data

SELECT

    s.id AS shop_id,

    s.name AS shop_name,

    i.status AS inventory_status,

    l.id AS location_id,

    l.uuid AS location_uuid

FROM locations l

JOIN shops s ON s.location_id = l.id

JOIN inventories i ON i.shop_id = s.id

WHERE

    l.address_id IN (1, 2, 3, 4, 5)  -- potentially thousands of values

;

where the values in l.address_id IN (1, 2, 3, 4, 5) come from the first query.

The query plans for the two split queries look simpler than the first one's, but I wonder if that in itself means that the second solution is better.

I know that inner joins are pretty well optimized, and that a single round-trip to the DB would be preferable.

What about memory usage? Or resource contention on the tables? (e.g. locks)