Basically, I take the same query as above and replace all occurences of
tables logs and tags with temp_logs and temp_tags, created as follow:
CREATE TEMPORARY TABLE temp_logs ON COMMIT DROP AS
SELECT * FROM logs WHERE condition ORDER BY date DESC LIMIT max_size;
CREATE TEMPORARY TABLE temp_tags ON COMMIT DROP AS
SELECT * FROM tags WHERE logid IN (SELECT logid FROM temp_logs);
With condition usually defining a date window. As we are experimenting
with this approach, date has become a forced criteria. I have
experimented with partitioning, but it led to the logid primary key not
being unique anymore, which was a problem when joining data with the
tags table.
So the queries are pretty much the same, the boost in speed being simply
due to the limitation of the search space.
How are you partitioning the tags? Is the partitioned query doing the
same job as the non partitioned query? Is date a forced criteria?
(and if it is, have you considered date partition/brute force?)
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
