Date: Wed, 26 Sep 2012 05:27:26 -0700 (PDT)
From: FFW_Rude <FFW_Rude@hotmail.com>
To: pgsql-performance@postgresql.org
Message-ID: <1348662446741-5725486.post@n5.nabble.com>
Subject: Same query doing slow then quick
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.