Skip site navigation (1) Skip section navigation (2)

problem with slow select

From: francescosaf <francescosaf(dot)152kao(at)mail(dot)webservertalk(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: problem with slow select
Date: 2004-04-21 15:00:23
Message-ID: francescosaf.152kao@mail.webservertalk.com (view raw or flat)
Thread:
Lists: pgsql-sql
hi 

I have two tables:

table:    XXXX
idcode varchar(15) -->references (idcode) table YYYY
latitude float
longitude float
time timestamp
p_a char(1)
barcode....
address....
idprog serial... -->primary key
flag boolean

table YYYY
idfact numeric(7,0) --->references .....
idcode varchar(15) --->primary key
name varchar(20)

I want to exctract the last operation of table XXXX for each idcode of
table YYYY where idfact=123

I HAVE THIS QUERY BUT IT IS TOO SLOW (10 SECONDS):

select XXXX.*,YYYY.name from YYYY,XXXX  join (select
XXXX.idcode,max(XXXX.tempo) as tempo from XXXX,YYYY where
YYYY.idfact=123 and XXXX.idcode=YYYY.idcode  group by XXXX.idcode)
temptable on (temptable.tempo=XXXX.tempo and
temptable.idcode=XXXX.idcode) where YYYY.idfact=123 and
XXXX.idcode=YYYY.idcode order by YYYY.name;

PLEASE HELP ME



--
francescosaf
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message189539.html
 

Responses

pgsql-sql by date

Next:From: abief_ag_-postgresqlDate: 2004-04-21 15:16:05
Subject: Trigger calling a function HELP ME! (2)
Previous:From: Denis KhabasDate: 2004-04-21 14:58:28
Subject: Which SQL command creates ExclusiveLock?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group