From: | Matthieu Huin <matthieu(dot)huin(at)wallix(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: temporary table as a subset of an existing table and indexes |
Date: | 2010-11-09 13:38:56 |
Message-ID: | 4CD94EF0.4040703@wallix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2010-11-09 13:55:14 | Re: Failed archive_command copy - number of attempts configurable? |
Previous Message | Merlin Moncure | 2010-11-09 12:50:37 | Re: Why facebook used mysql ? |