I'm a noob in writing efficient Postgres queries, so I wrote a first function
to query multiple linked tables using the PostGIS extension. The query should
fetch data from multiple tables and finally give me a table with two columns.
Here's the code:
[code]
prepare getmydata(real,real,real) AS (
with
closeby(id) AS (
select buildingid from info where ST_DWithin(position, 'POINT($1 $2)',
$3)
),
closebuildings(descriptionid,image) AS (
select descriptionid,image from buildings where id IN (select * from
closeby)
),
closebuildingdescriptions(data) AS (
select data from buildingdescriptions where id IN (select descriptionid
from closebuildings)
)
select image,data from closebuildings,closebuildingdescriptions;
);
execute getmydata(0.0,0.0,10.0);
[/code]
Actually the problem is that this query is VERY slow, even if the database
content is small (taking around 15 minutes or so). The problem seems to be that
postgres has to make sure that for the select statement both columns have equal
length. If I only do "select image from closebuildings", the results are
delivered in 0.1 secs, a "select data from closebuildingdescriptions" is
delivered in 7.8 secs.
I ran an "explain analyze" call, but I can't make any sense from the output:
[code]
"Nested Loop (cost=7816.51..2636821.06 rows=131352326 width=36) (actual
time=117.125..6723.014 rows=12845056 loops=1)"
" CTE closeby"
" -> Seq Scan on info (cost=0.00..1753.11 rows=186 width=4) (actual
time=0.022..5.821 rows=1579 loops=1)"
" Filter: (("position" &&
'0103000020797F000001000000050000007D3F35DEAC512041E3A59BBC9BE153417D3F35DEAC512041E3A59BBCA0E153417D3F35DED4512041E3A59BBCA0E153417D3F35DED4512041E3A59BBC9BE153417D3F35DEAC512041E3A59BBC9BE15341'::geometry)
AND ('0101000020797F00007D3F35DEC0512041E3A59B3C9EE15341'::geometry &&
st_expand("position", 10::double precision)) AND _st_dwithin("position",
'0101000020797F00007D3F35DEC0512041E3A59B3C9EE15341'::geometry, 10::double
precision))"
" CTE closebuildings"
" -> Hash Semi Join (cost=6.04..4890.03 rows=1351 width=8) (actual
time=54.743..61.025 rows=3584 loops=1)"
" Hash Cond: (closebuildings.id = closeby.buildingid)"
" -> Seq Scan on closebuildings (cost=0.00..4358.52 rows=194452
width=12) (actual time=0.042..31.646 rows=194452 loops=1)"
" -> Hash (cost=3.72..3.72 rows=186 width=4) (actual
time=7.073..7.073 rows=1579 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 56kB"
" -> CTE Scan on closeby (cost=0.00..3.72 rows=186 width=4)
(actual time=0.023..6.591 rows=1579 loops=1)"
" CTE closebuildingdescriptions"
" -> Nested Loop (cost=30.40..1173.37 rows=97226 width=516) (actual
time=117.103..1890.902 rows=3584 loops=1)"
" -> HashAggregate (cost=30.40..32.40 rows=200 width=4) (actual
time=63.529..66.176 rows=3584 loops=1)"
" -> CTE Scan on closebuildings (cost=0.00..27.02 rows=1351
width=4) (actual time=54.746..62.316 rows=3584 loops=1)"
" -> Index Scan using buildingdescriptions_pkey on
buildingdescriptions (cost=0.00..5.69 rows=1 width=520) (actual
time=0.506..0.507 rows=1 loops=3584)"
" Index Cond: (id = closebuildings.descriptionid)"
" -> CTE Scan on closebuildingdescriptions (cost=0.00..1944.52 rows=97226
width=32) (actual time=117.115..1901.993 rows=3584 loops=1)"
" -> CTE Scan on closebuildings (cost=0.00..27.02 rows=1351 width=4) (actual
time=0.000..0.536 rows=3584 loops=3584)"
"Total runtime: 7870.567 ms"
[/code]
If anyone can come up with a solution or a suggestion how to solve this, I
would highly appreciate it.
Cheers
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
