Re: Postgres slowdown on large table joins

On Fri, Feb 16, 2001 at 01:32:13PM -0500, Dave Edmondson wrote:
> SELECT *
> FROM data
> ORDER BY ts desc
> LIMIT 1
>
> ...takes about 16-26 seconds - still sloooow, but not as bad as with the
> table join. What's really causing the slowdown? ...should I just execute
> the command differently? I'm trying to get the latest data in all three
> tables.

How much does it take to

SELECT MAX(ts) FROM data;

Definitely should be much faster than sorting.
If it's faster, try this

SELECT c.unit_id,c.name,c.auxenable,c.bias,c.feedback,c.gain,c.igain,
c.mode,c.reverse,c.setpoint,c.switch1,c.switch2,c.timeout,
c.valvetype,d.active,d.drive_1_s,d.drive_1_f,d.drive_2_s,
d.drive_2_f,d.mval,d.m4val,d.sw1,d.sw2,d.cycle,d.itemp,
d.error,d.aval,d.ts,c.ts,p.degree,c.outputa,c.outputb,
c.outputc,c.rawtemp
FROM config c, data d, prefs p
WHERE c.conf_id = '4'
AND d.conf_id = c.conf_id
AND p.conf_id = c.conf_id
AND d.ts = (SELECT max(dd.ts) FROM data dd WHERE dd.conf_id = '4');

If data.ts fields are not unique for the given conf_id, you can get more than
one row from this query, but then you can LIMIT it again.

I think, that indices could be useful for finding min/max. PosgreSQL doesn't
seem do use them for that work. It would be easy with simple queries like
'SELECT max(ts) FROM data'.

Harder for more complex queries, say:

SELECT max(ts) FROM data WHERE conf_id = '4'

It wouldn't suffice to find the rightmost btree element, but if we had two
column index on (conf_id, ts)...? Search for rightmost ('4', X) ? Am I missing
something or this would be possible?

greetings for all postgresql developers and users!
tom

--
.signature: Too many levels of symbolic links