Same query doing slow then quick

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux