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écsi Széchenyi téren. http://ad.adverticum.net/b/cl,1,6022,176377,235993/click.prm
