From: | Dániel Dénes <panther-d(at)freemail(dot)hu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | UNION ALL with the same ORDER BY on the parts and the result |
Date: | 2007-06-14 19:55:34 |
Message-ID: | freemail.20070514215534.80609@fm04.freemail.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-06-14 19:59:46 | Re: Function with COPY command? |
Previous Message | Rikard Pavelic | 2007-06-14 19:18:13 | Re: explain analyze on a function |