Hi,
I've got a table that stores private messages (like e-mails). Every row
has a source_user, a destination_user, a timestamp, and information
on whether the source and/or the destination user has already deleted
the message.
> CREATE TABLE privs (
> id serial NOT NULL,
> src_usrid integer NOT NULL,
> src_del boolean NOT NULL,
> dst_usrid integer NOT NULL,
> dst_del boolean NOT NULL,
> timest timestamp with time zone NOT NULL,
> content text NOT NULL,
> CONSTRAINT privs_chk_noself CHECK ((src_usrid <> dst_usrid))
> );
There are two indices:
> srcusrid_timest: (src_usrid, timest) WHERE (src_del IS FALSE)
> dstusrid_timest: (dst_usrid, timest) WHERE (dst_del IS FALSE)
The query I would like to optimize:
> SELECT * FROM ((
> SELECT * FROM privs
> WHERE src_usrid = 1 AND src_del IS FALSE
> ORDER BY timest DESC
> ) UNION ALL (
> SELECT * FROM privs
> WHERE dst_usrid = 1 AND dst_del IS FALSE
> ORDER BY timest DESC
> )) AS data
> ORDER BY timest DESC
--------
I think the UNION ALL could be done like a "merge join", ie. scanning
both subqueries simultaneously using the indices, and always adding
the row with the greather timestamp to the result. But it appends the
resultsets, and then does a sort.
When I tried to do this with one query like:
> WHERE (src_usrid = 1 AND src_del IS FALSE)
> OR (dst_usrid = 1 AND dst_del IS FALSE)
> ORDER BY timest DESC
it chose to do a bitmap-or and then a sort.
I'd like to avoid that sort, because it won't scale up very good as the
table grows... is there a way I can do that? I can only think of self-
made a function doing exactly the same that i wrote above...
Regards,
Denes Daniel
35% kedvezmény az Osiris Kiadó köteteire. TÉRjen be: egész héten várjuk programjainkkal az Alexandra Könyvtéren, a
pécsiSzéchenyi téren.
http://ad.adverticum.net/b/cl,1,6022,176377,235993/click.prm
