I have the following PostgreSQL schema:
CREATE TABLE User (
ID INTEGER PRIMARY KEY
);
CREATE TABLE BOX (
ID INTEGER PRIMARY KEY
);
CREATE SEQUENCE seq_item;
CREATE TABLE Item (
ID INTEGER PRIMARY KEY DEFAULT nextval('seq_item'),
SENDER INTEGER REFERENCES User(id),
RECEIVER INTEGER REFERENCES User(id),
INFO TEXT,
BOX_ID INTEGER REFERENCES Box(id) NOT NULL,
ARRIVAL TIMESTAMP
);
Its main use case is a typical producer/consumer scenario. Different users may insert an item in the database in a particular box for a particular user and each user can retrieve the topmost(this means the oldest) item in a box that is addressed to her/him. It more or less mimics the functionality of a queue on a database level.
More precisely, the most common operations are the following:
INSERT INTO ITEM(SENDER, RECEIVER, INFO, BOX_ID, ARRIVAL)
VALUES (nsid, nrid, ncontent, nqid, ntime);
And retrieve commands based on a combination of either RECEIVER+SENDER
or RECEIVER+BOX_ID
:
SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.BOX_ID=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;
and
SELECT * INTO it FROM Item i WHERE (i.RECEIVER=? OR i.RECEIVER is NULL) AND
(i.SENDER=?) ORDER BY ARRIVAL LIMIT 1;
DELETE FROM Item i WHERE i.id=it.id;
The last two snippets are packed within a stored procedure.
I was wondering how to achieve best performance given this use case and knowing that the users will insert and retrieve somewhere between 50,000
and 500,000
items (however, the database is never expected to contain more than 100,000
items at a given point)?
EDIT
This is the EXPLAIN
I get with for the SELECT
statements no indexes:
Limit (cost=23.07..23.07 rows=1 width=35)
-> Sort (cost=23.07..25.07 rows=799 width=35)
Sort Key: ARRIVAL
-> Seq Scan on Item i (cost=0.00..19.07 rows=799 width=35)
Filter: (((RECEIVER = 1) OR (RECEIVER IS NULL)) AND (SENDER = 1))
The best EXPLAIN
I get based on my understanding is when I put an index on the time(CREATE INDEX ind ON Item(ARRIVAL);
):
Limit (cost=0.42..2.88 rows=1 width=35)
-> Index Scan using ti on Item i (cost=0.42..5899.42 rows=2397 width=35)
Filter: (((receiver = 2) OR (RECEIVER IS NULL)) AND (SENDER = 2))
In all of the cases without index on ARRIVAL
I have to sort the table which seems to my inefficient. If I try to combine an index on ARRIVAL
and RECEIVER/SENDER
I get the same explanation, but slightly slower.
Is it correct to assume that a single index on ARRIVAL
is the most efficient option?
select ... into ..
to create a table is discouraged. You should use the standard compliantcreate table as
instead.user
is a reserved word and illegal as identifier without double quotes. The syntax of yourDELETE
statements is wrong. Your question would be more useful if you provided a working test case. And, as always, your version of Postgres. Also consider using aserial
column. And most importantly, your whole idea is not safe for concurrent use. Multiple transactions can fetch the same row before it is deleted. Related: dba.stackexchange.com/a/69497/3684