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
create table
statements for the tables in question (including all indexes) and the execution plan generated usingexplain (analyze, verbose)
. Formatted text please, no screen shots – a_horse_with_no_name Jul 26 at 14:50