-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Greg Sabino Mullane (in the other thread) suggested the following alternate,
> but it ran slightly slower on my machine than the original.
>
> SELECT id, date, value FROM sample a WHERE date <='2003-01-01' AND oid =
> (SELECT oid FROM sample WHERE id = a.id AND DATE <='2003-01-01' ORDER BY
> date DESC LIMIT 1)
> ORDER BY id;
You should check this again. There is no way that my query is slower, as
long as you have an index on the "date" column and have run an ANALYZE on
the "sample" table. Try running these:
ANALYZE sample;
CREATE INDEX sample_date on sample(date);
EXPLAIN <the query above>;
...and make sure that it is hitting the sample_date index. I also just
realized that if you have pre-existing indexes, they may be slowing my
query down (e.g. one on "date" *and* "id"). Make sure that the explain
above only uses the sample_date index.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200302071047
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE+Q9VdvJuQZxSWSsgRAjHVAJwP6Qw4L+gOpfCC6yYkgIgJjzkcZQCggZ1y
2ckvZhgeUpQYOByPxHNKOmk=
=ix4n
-----END PGP SIGNATURE-----