I have a table, and I need to get the last cell of a column in it. The table is large though, and I want to do it as quickly as possible. The trick is that the newest rows (largest timestamp) are added to the bottom of the table. So I really just need to figure out the fastest way to get the last row. I'd like to do this without making an index or permanently modifying the table or creating tables(EDIT: I'm open to making views)
I've tried this:
SELECT my_column FROM my_schema.my_table ORDER by timestamp DESC NULLS LAST LIMIT 1
but it was really slow for the large table
so I tried this:
SELECT my_column FROM my_schema.my_table WHERE timestamp=(select max(timestamp) from my_schema.my_table)
and it felt even slower.
Any thoughts?
You could use a little tricky way and keep your last inserted row in a separate table.
Notice, that the INSERT query can return values:
INSERT INTO ... RETURNING *;
Now you can use it and save new row in a separate table:
INSERT INTO my_schema.my_table_last_row(INSERT INTO ... RETURNING *);
The table my_table_last_row should have the same columns (or can just INHERIT from the original table).
You could use a trigger ON INSERT to do the job.
CREATE OR REPLACE FUNCTION remember_last_row()
RETURNS trigger AS
$BODY$
BEGIN
TRUNCATE my_schema.my_table_last_row;
INSERT INTO my_schema.my_table_last_row VALUES(NEW.*);
RETURN NEW;
END;
$BODY$
CREATE TRIGGER remember_last_row_trigger
BEFORE INSERT
ON my_schema.my_table
FOR EACH ROW
EXECUTE PROCEDURE remember_last_row();
I think that this is the fastest way of getting the last row inserted without any modifications to the original table (including adding an index).
Of course things get complicated if you update that row - you would need to keep the copy updated as well.
Simpler solution - store just the last timestap ;)