Same query doing slow then quick

From: FFW_Rude <FFW_Rude(at)hotmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Same query doing slow then quick
Date: 2012-09-26 12:27:26
Message-ID: 1348662446741-5725486.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Grittner 2012-09-26 12:58:41 Re: Postgres becoming slow, only full vacuum fixes it
Previous Message Kiriakos Tsourapas 2012-09-26 09:41:46 Re: Postgres becoming slow, only full vacuum fixes it