CODEWITHSUNDEEP
sqlpostgresqlpostgispostgresql-9.4
Taras
Taras

Reputation: 46

Speed up the search in a PostgreSQL

How to speed up the search in a PostgreSQL database? There are three tables: table_a, table_b, table_c. Problem that table_a has 450 000 records, table_b has 8 300 000 records, table_c has 1 180 000 records.
This query has to wait for about 15 minutes: 

    select  table_c.* from table_c
left join table_b on table_b.id = table_c.table_b_id
left join table_a on table_a.id = table_b.table_a_id
where table_a.id = 1

The number of rows may not exceed 500. When I set a limit of up to 500, the query time becomes within 1 second.

Create info: 

CREATE TABLE table_a
(
  id serial NOT NULL,
  latitude numeric(10,8) NOT NULL,
  longitude numeric(11,8) NOT NULL,
  CONSTRAINT table_a_pkey PRIMARY KEY (id),
)

CREATE TABLE table_b
(
  id serial NOT NULL,
  table_a_id integer,
  CONSTRAINT table_b_pkey PRIMARY KEY (id),
  CONSTRAINT table_b_table_a_id_fkey FOREIGN KEY (table_a_id)
      REFERENCES table_a (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE table_c
(
  id serial NOT NULL,
  table_b_id integer,
  geom geometry(MultiPolygon,4326),
  CONSTRAINT table_c_pkey PRIMARY KEY (id),
  CONSTRAINT table_c_table_b_id_fkey FOREIGN KEY (table_b_id)
      REFERENCES table_b (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Additional info:

Upvotes: 0

Views: 364

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

First, the left joins are not needed because the where clause turns them into inner joins anyway. So rewrite the query as:

select  c.*
from table_c c join
     table_b b
     on b.id = c.table_b_id join
     table_a a
     on a.id = b.table_a_id
where a.id = 1;

Then the following indexes should help: table_a(id), table_b(table_a_id, id), and table_c(table_b.b_id).

Actually, the query can be simplified, because the table_a id is in table_b:

select  c.*
from table_c c join
     table_b b
     on b.id = c.table_b_id 
where b.table_a_id = 1;

The first index is not needed for this query.

Finally, if this query might return duplicates and you do not want them, then you might consider:

select c.*
from table_c c
where exists (select 1
              from table_b b
              where b.id = c.table_b_id and b.table_a_id = 1
             );

Upvotes: 1

Related Questions