Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →

The db structure is more or less like this and cant be modified. I can only add some indexes but cant set for example foreign keys:

    CREATE TABLE adresspoint
(
  id character varying(3999) NOT NULL,
  "position" geometry(PointZ,25832)
  CONSTRAINT pk_adresspoint PRIMARY KEY (id)
)

CREATE INDEX adresspoint_idx_position
  ON adresspoint
  USING gist
  ("position");

CREATE TABLE house
(
  id character varying(3999) NOT NULL,
  adresspointid character varying(3999),
  CONSTRAINT pk_house PRIMARY KEY (id)
)

CREATE INDEX house_idx_adresspointid
  ON house
  USING btree
  (adresspointid COLLATE pg_catalog."default");

I have query like that:

SELECT DISTINCT house.id 
FROM house 
   LEFT JOIN adresspoint ON adresspoint.id = house.adresspointid 
             AND st_contains(st_makeenvelope('562827','6326412','563365','6326590',25832),adresspoint.position)
OFFSET 0 LIMIT 10

and it runs very quickly (100 ms) but when I change it to:

SELECT DISTINCT house.id 
FROM house 
  LEFT JOIN adresspoint ON adresspoint.id = house.adresspointid 
        AND st_contains(st_makeenvelope('562827','6326412','563365','6326590',25832),adresspoint.position)
WHERE adresspoint.id is not null -- ***change is here***
OFFSET 0 LIMIT 10

It runs very slowly (like 100 times slower).

I wonder what is the reason behind it and is there any way I can optimize it? I have indexes on position, ids etc.

Here are execution plans:

faster:

Limit  (cost=657.35..9907.84 rows=10 width=86) (actual time=6.864..7.036 rows=10 loops=1)
         ->  Nested Loop Anti Join  (cost=657.35..632794743.21 rows=6682119 width=86) (actual time=6.852..6.978 rows=10 loops=1)
               Join Filter: ((adresspoint.id)::text = (house.adresspointid)::text)
               ->  Index Scan using pk_house on pk_house house (cost=0.56..17531908.09 rows=6841197 width=123) (actual time=0.077..0.159 rows=10 loops=1)
               ->  Materialize  (cost=656.80..68209.88 rows=5995 width=37) (actual time=0.678..0.678 rows=0 loops=10)
                     ->  Bitmap Heap Scan on adresspoint  (cost=656.80..68179.90 rows=5995 width=37) (actual time=6.757..6.757 rows=0 loops=1)
                           Recheck Cond: ('0103000020E86'::geometry ~ "position")
                           Filter: _st_contains('010300002'::geometry, "position")
                           ->  Bitmap Index Scan on adresspoint_idx_position  (cost=0.00..655.30 rows=17984 width=0) (actual time=6.737..6.737 rows=0 loops=1)
                                 Index Cond: ('0103000020E86'::geometry ~ "position")
 Planning time: 22.333 ms
 Execution time: 7.634 ms

slower:

 Limit  (cost=657.35..40441.29 rows=10 width=86) (actual time=24242.233..24242.233 rows=0 loops=1)
         ->  Nested Loop  (cost=657.35..632794743.21 rows=159058 width=86) (actual time=24242.225..24242.225 rows=0 loops=1)
               Join Filter: ((house.adresspointid)::text = (adresspoint.id)::text)
               ->  Index Scan using pk_house on pk_house house  (cost=0.56..17531908.09 rows=6841197 width=123) (actual time=0.074..12008.484 rows=8222065 loops=1)
               ->  Materialize  (cost=656.80..68209.88 rows=5995 width=37) (actual time=0.001..0.001 rows=0 loops=8222065)
                     ->  Bitmap Heap Scan on adresspoint (cost=656.80..68179.90 rows=5995 width=37) (actual time=5.294..5.294 rows=0 loops=1)
                           Recheck Cond: ('0103000020E8640000010'::geometry ~ "position")
                           Filter: ((id_lokalid IS NOT NULL) AND _st_contains('0103000020E864'::geometry, "position"))
                           ->  Bitmap Index Scan on adresspoint_idx_position  (cost=0.00..655.30 rows=17984 width=0) (actual time=5.286..5.286 rows=0 loops=1)
                                 Index Cond: ('0103000020E'::geometry ~ "position")
 Planning time: 17.286 ms
 Execution time: 24242.705 ms
share|improve this question
1  
Please edit your question add the create table statements for the tables in question (including all indexes) and the execution plan generated using explain (analyze, verbose). Formatted text please, no screen shots – a_horse_with_no_name Jul 26 at 14:50
    
Ok done, can you check now? @a_horse_with_no_name – kulawyjoe Jul 27 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.