Postgresql – Join 3 tables of a one-to-many relationship

join;postgresql

I have 3 files. 1 shapefile and 2 DBF files. For those of who are not a familiar with shapefiles (GIS data format), they are essentially database tables with rows and columns that have spatial geometry such as point, line, polygon etc., which can be displayed visually and used in calculations in a GIS software.

trucknetwork (shapefile) – 564 rows (column sri links to table1) – specific truck road network in NJ with no road names:

gid integer NOT NULL DEFAULT nextval('trucknetwork_gid_seq'::regclass),
id numeric(10,0),
sri character varying(20),
mp_start double precision,
mp_end double precision,
descriptio character varying(50),
road_type character varying(50),
geom geometry(MultiLineStringM),
CONSTRAINT trucknetwork_pkey PRIMARY KEY (gid)

table1 – 488715 rows (links to shapefile with sri column and table2 with seg_guid column) – start and end points of all NJ roads:

gid integer NOT NULL DEFAULT nextval('table1_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
sri character varying(20),
route_type numeric,
sld_mp_st numeric,
sld_mp_end numeric,
CONSTRAINT table1_pkey PRIMARY KEY (gid)

table2 – 669557 rows (links to table1 with seg_guid column) – all NJ road names:

gid integer NOT NULL DEFAULT nextval('table2_gid_seq'::regclass),
objectid integer,
seg_guid character varying(38),
s_name character varying(254),
CONSTRAINT table2_pkey PRIMARY KEY (gid)

I want to join these 3 tables into 1 table – with the result being 564 rows of each road segment with its correct name. It is a one-to-many relationship.

I have posted this question on GIS stackexchange but I did not get a lot of feedback and I want to do this task in SQL not a GIS software.

I have tried left join, inner join – and I have tried the group by but for every query I keep getting over 100,000 + records

select shp.descriptio as descr, shp.road_type as road_type, shp.geom as geom, t2.s_name as street_name 
      into networkEX 
from trucknetwork as shp 
   join table1 as t1 on shp.sri = t1.sri 
   join table2 as t2 on t1.seg_guid = t2.seg_guid;

I am not sure what the logic should be. Subquery? Do I have to break this up into smaller steps?

Best Answer

Obviously, there can be multiple rows with the same value in table1.sri and / or in table2.seg_guid. That's the only logical explanation for the multiplied row count in your query result.

Which of both is impossible to tell from the information you provided. But the name "trucknetwork" indicates there should be many "street names" in the result, so your task seems contradictory at the outset.

My educated guess is that the multiplication happens in table1 (many start and end points of roads in the same truck network). There are various techniques to reduce to a single pick. The best one depends on information not in the question. Since you get so many result rows, a LATERAL join with LIMIT 1 is a hot contender. Be sure to support it with an index.

Since you want to join to exactly 1 row in the result for each row in trucknetwork, you have to define which row to pick from a set of multiple matches. More information we don't have. I pick an arbitrary winner.

And there is also the possibility that some of the rows in trucknetwork might find no matching row in table1 and/or table2 at all. In an ideal world, referential integrity would be enforced with FOREIGN KEY constraints. To make do with what we have, use LEFT [OUTER] JOIN to avoid eliminating trucknetwork rows (which would probably indicate data errors in table1 or table2).

So:

CREATE TABLE network_ex AS
SELECT shp.descriptio AS descr, shp.road_type, shp.geom, t2.s_name AS street_name 
FROM   trucknetwork shp
LEFT   JOIN LATERAL (
   SELECT seg_guid
   FROM   table1
   WHERE  sri = shp.sri
   -- ORDER  BY ???      -- define which one to pick if that matters
   LIMIT  1              -- ! just the one !
   ) t1 ON true
LEFT   JOIN table2 t2 USING (seg_guid);

Ideally, you would have these two multicolumn indexes to make this fast:

CREATE INDEX table1_sri_seg_guid_idx ON table1 (sri, seg_guid);
CREATE INDEX table2_seg_guid_s_name_idx ON table2 (seg_guid, s_name);

If there can be duplication in table2 (as well), reduce to a single match in a similar fashion. Again, the best technique depends on the complete picture.

Alternatively, you could aggregate all (distinct) road names into a single string or array in the result ...

Asides:

Use CREATE TABLE AS. SELECT INTO is discouraged:
- Creating temporary tables in SQL
Avoid unquoted CaMeL-case identifiers in Postgres.
- Are PostgreSQL column names case-sensitive?

Better Queries

This is just your query reformatted with aliases and some noise removed to clear the fog:

SELECT count(DISTINCT t.id)
FROM   tickets      t
JOIN   transactions tr ON tr.objectid = t.id
JOIN   attachments  a  ON a.transactionid = tr.id
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

Most of the problem with your query lies in the first two tables tickets and transactions, which are missing from the question. I'm filling in with educated guesses.

t.status, t.objecttype and tr.objecttype should probably not be text, but enum or possibly some very small value referencing a look-up table.

`EXISTS` semi-join

Assuming tickets.id is the primary key, this rewritten form should be much cheaper:

SELECT count(*)
FROM   tickets t
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id
AND    EXISTS (
   SELECT 1
   FROM   transactions tr
   JOIN   attachments  a ON a.transactionid = tr.id
   WHERE  tr.objectid = t.id
   AND    tr.objecttype = 'RT::Ticket'
   AND    a.contentindex @@ plainto_tsquery('frobnicate')
   );

Instead of multiplying rows with two 1:n joins, only to collapse multiple matches in the end with count(DISTINCT id), use an EXISTS semi-join, which can stop looking further as soon as the first match is found and at the same time obsoletes the final DISTINCT step. Per documentation:

The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion.

Effectiveness depends on how many transactions per ticket and attachments per transaction there are.

Determine order of joins with `join_collapse_limit`

If you know that your search term for attachments.contentindex is very selective - more selective than other conditions in the query (which is probably the case for 'frobnicate', but not for 'problem'), you can force the sequence of joins. The query planner can hardly judge selectiveness of particular words, except for the most common ones. Per documentation:

join_collapse_limit (integer)

[...]
Because the query planner does not always choose the optimal join order, advanced users can elect to temporarily set this variable to 1, and then specify the join order they desire explicitly.

Use SET LOCAL for the purpose to only set it for the current transaction.

BEGIN;
SET LOCAL join_collapse_limit = 1;

SELECT count(DISTINCT t.id)
FROM   attachments  a                              -- 1st
JOIN   transactions tr ON tr.id = a.transactionid  -- 2nd
JOIN   tickets      t  ON t.id = tr.objectid       -- 3rd
WHERE  t.status <> 'deleted'
AND    t.type = 'ticket'
AND    t.effectiveid = t.id
AND    tr.objecttype = 'RT::Ticket'
AND    a.contentindex @@ plainto_tsquery('frobnicate');

ROLLBACK; -- or COMMIT;

The order of WHERE conditions is always irrelevant. Only the order of joins is relevant here.

Or use a CTE like @jjanes explains in "Option 2". for a similar effect.

Indexes

B-tree indexes

Take all conditions on tickets that are used identically with most queries and create a partial index on tickets:

CREATE INDEX tickets_partial_idx
ON tickets(id)
WHERE  status <> 'deleted'
AND    type = 'ticket'
AND    effectiveid = id;

If one of the conditions is variable, drop it from the WHERE condition and prepend the column as index column instead.

Another one on transactions:

CREATE INDEX transactions_partial_idx
ON transactions(objecttype, objectid, id)

The third column is just to enable index-only scans.

Also, since you have this composite index with two integer columns on attachments:

"attachments3" btree (parent, transactionid)

This additional index is a complete waste, delete it:

"attachments1" btree (parent)

Details:

Is a composite index also good for queries on the first field?

GIN index

Add transactionid to your GIN index to make it a lot more effective. This may be another silver bullet, because it potentially allows index-only scans, eliminating visits to the big table completely.
You need additional operator classes provided by the additional module btree_gin. Detailed instructions:

Inner join using an array column

"contentindex_idx" gin (transactionid, contentindex)

4 bytes from an integer column don't make the index much bigger. Also, fortunately for you, GIN indexes are different from B-tree indexes in a crucial aspect. Per documentation:

A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Bold emphasis mine. So you just need the one (big and somewhat costly) GIN index.

Table definition

Move the integer not null columns to the front. This has a couple of minor positive effects on storage and performance. Saves 4 - 8 bytes per row in this case.

                      Table "public.attachments"
         Column      |            Type             |         Modifiers
    -----------------+-----------------------------+------------------------------
     id              | integer                     | not null default nextval('...
     transactionid   | integer                     | not null
     parent          | integer                     | not null default 0
     creator         | integer                     | not null default 0  -- !
     created         | timestamp                   |                     -- !
     messageid       | character varying(160)      |
     subject         | character varying(255)      |
     filename        | character varying(255)      |
     contenttype     | character varying(80)       |
     contentencoding | character varying(80)       |
     content         | text                        |
     headers         | text                        |
     contentindex    | tsvector                    |

Best Answer

Related Solutions

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Postgresql – Slow fulltext search due to wildly inaccurate row estimates