2

It is known that left join is generally slower than inner join, but this difference seems out of proportion. I have table A with 2,542,526 rows and table B with 30,444 rows. I ran this query, which returned values in just 37.6 seconds:

SELECT A.*, B.column_a 
INTO C FROM A, B
WHERE ST_Within(A.geom, B.geom) 
AND ST_DWithin(A.geom, B.geom, 0.000524)

Then, I ran this query (basically the same tables and columns but with left join. I wanted to get the extra info that a left join provides).

SELECT A.*, B.column_a 
INTO D FROM A 
LEFT JOIN B 
ON ST_Within(A.geom, B.geom) 
AND ST_DWithin(A.geom, B.geom, 0.000524)

That second query ran for 15 hours 30 minutes (hasn't finished yet, still running).

There are GIST indexes on both geom columns. Why is it taking so long? Am I missing something here? I knew it would take longer, but 15 hours seems out of proportion. I'm not very experienced with left joins, so my guess is that I am doing something wrong. I would like to know if I am making some kind of efficiency mistake or if this is normal.

Explain for first query:

"Nested Loop  (cost=0.41..218269.24 rows=9 width=479)"
"  ->  Seq Scan on B  (cost=0.00..9799.44 rows=30444 width=4140)"
"  ->  Index Scan using latlonind on A (cost=0.41..6.84 rows=1 width=473)"
"        Index Cond: ((B.geom ~ geom) AND (geom && st_expand(B.geom, '0.000524'::double precision)))"
"        Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"

Explain for second query:

"Nested Loop Left Join  (cost=0.00..65359548445.44 rows=2542526 width=479)"
"  Join Filter: ((B.geom ~ A.geom) AND (A.geom && st_expand(B.geom, '0.000524'::double precision)) AND (B.geom && st_expand(A.geom, '0.000524'::double precision)) AND _st_contains(B.geom, A.geom) AND _st_dwithin(v (...)"
"  ->  Seq Scan on A (cost=0.00..281803.26 rows=2542526 width=473)"
"  ->  Seq Scan on B (cost=0.00..9799.44 rows=30444 width=4140)"

EDIT

Other important info (answering comments) is:

-Postgres version 9.5.15.

-Select Count (*) from A takes 250 msec.

-The first query returns 2,197,883 rows, which is almost the same number of results that the left join would return.

-When I do the explain(analyze, buffers) for both queries without the into, it doesn't end for the second one (I had it running for half an hour and didn't finish). The first one took 26 seconds (almost the same as executing the query), getting these results:

"Nested Loop  (cost=0.41..218269.24 rows=9 width=479) (actual time=0.111..26762.163 rows=2197883 loops=1)"
"  Buffers: shared hit=14121642 read=212529"
"  ->  Seq Scan on B  (cost=0.00..9799.44 rows=30444 width=4140) (actual time=0.004..29.747 rows=30444 loops=1)"
"        Buffers: shared hit=9 read=9486"
"  ->  Index Scan using geom on A  (cost=0.41..6.84 rows=1 width=473) (actual time=0.108..0.866 rows=72 loops=30444)"
"        Index Cond: ((B.geom ~ geom) AND (geom&& st_expand(B.geom, '0.000524'::double precision)))"
"        Filter: ((B.geom && st_expand(geom, '0.000524'::double precision)) AND _st_contains(B.geom, geom) AND _st_dwithin(geom, B.geom, '0.000524'::double precision))"
"        Rows Removed by Filter: 102"
"        Buffers: shared hit=14093482 read=196104"
"Planning time: 0.219 ms"
"Execution time: 26825.136 ms"

-I included a ST_DWithin() to see if it was a spatial analysis issue (which shouldn't be in the first place, since the inner join worked), having it and removing it doesn't change the output, it still won't finish.

EDIT 2:

After requested in the comments, I set enable_seqscan = off and the explain showed the following:

"Nested Loop Left Join  (cost=20000000000.00..79567050305.82 rows=25692511 width=461)"
"  Join Filter: ((B.geom ~ A.geom) AND _st_contains(B.geom, A.geom))"
"  ->  Seq Scan on A (cost=10000000000.00..10000157930.81 rows=2531781 width=455)"
"  ->  Materialize  (cost=10000000000.00..10000025348.66 rows=30444 width=4136)"
"        ->  Seq Scan on B  (cost=10000000000.00..10000009736.44 rows=30444 width=4136)"
  • 1
    Your second query does not limit the rows retrieved from table a, so it's not surprising that a query that returns over 2 million rows is slower than one that returns 9. And writing those 2 million rows will also take some time. But 15 hours seems way too long for that nonetheless. It would be interesting to see the execution plan generated using explain (analyze, buffers) - at least for the select part (without writing the new table). How long does a select count(*) from table_A take? – a_horse_with_no_name Feb 26 at 8:00
  • What is your exact Postgres version (select version(); will tell you) – a_horse_with_no_name Feb 26 at 8:01
  • What is the purpose of this combination of ST_Within and ST_DWithin? – CL. Feb 26 at 8:02
  • @a_horse_with_no_name Sorry, I forgot to add that info to the question. I will edit it. – A.T. Feb 26 at 8:19
  • 1
    The fact that Postgres expects 9 rows but gets 2 million means that the statistics are wrong. Does running analyze on both tables change anything? – a_horse_with_no_name Feb 26 at 8:44

Your Answer

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

Browse other questions tagged or ask your own question.