Hi,
I'm new here so i hope i don't do mistakes.
I'm having a serious performance issue in postgresql.
I have tables containing adresses with X,Y GPS coordinates and tables with
zoning and square of gps coordinates.
Basicly it looks like
adresses_01 (id,X,Y)
gps_01 (id,x_min,x_max,y_min,y_max).
[code]
"
SELECT
t2.id,
FROM
tables_gps.gps_01 t1
INNER JOIN
tables_adresses.adresses_01 t2
ON
t2."X" BETWEEN t1.x_min AND t1.x_max AND t2."Y" BETWEEN t1.y_min AND
t1.y_max
WHERE
t2.id='0'
"
[/code]
I have something like 250000rows in each table.
Now when i execute this on adresses_01 and gps_01, the request complete in a
few minutes.
But when doing it on adresses_02 and gps_02 (same number of rows
approximately) the query takes 5hours.
I have indexes on adresses on X,Y and an index in gps on
x_min,y_min,x_max,y_max.
Now i do updates in result of this query on ID (so i have an index on ID
too).
My question is ... Why ? (;o). And also, do i need to use CLUSTER (i don't
really understand what it does). And if so. Do i need to CLUSTER the id ? Or
the X,Y index ?
It may be not really clear so just ask questions if you don't get when i
mean or if you need specs or anything. I just moved from MySql to PostgreSql
last month.
Thanks in advance :)
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Same-query-doing-slow-then-quick-tp5725486.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
