Re: scale up (postgresql vs mssql) - Mailing list pgsql-performance

From Merlin Moncure
Subject Re: scale up (postgresql vs mssql)
Date
Msg-id CAHyXU0z1Ff5Q_5G_b8nPmKyMiR8+gjyr2qaBkBsLCDpRD1psKg@mail.gmail.com
Whole thread Raw
In response to Re: scale up (postgresql vs mssql)  (Andy Colson <andy@squeakycode.net>)
List pgsql-performance
On Wed, Jun 20, 2012 at 8:43 AM, Andy Colson <andy@squeakycode.net> wrote:
>> this is an obligation from the past:
>> http://archives.postgresql.org/pgsql-performance/2012-05/msg00017.php
>>
>> the same test, that did ~230 results, is now doing ~700 results. that
>> is, BTW even better than mssql.
>>
>> the ultimate solution for that problem was to NOT to do "ON COMMIT
>> DELETE ROWS" for the temporary tables. instead, we just do "DELETE FROM
>> temp_table1".
>>
>> doing "TRUNCATE temp_table1" is defiantly the worst case (~100 results
>> in the same test). this is something we knew for a long time, which is
>> why we did "ON COMMIT DELETE ROWS", but eventually it turned out as far
>> from being the best.
>>
>> another minor issue is that when configuring
>>  temp_tablespace='other_tablespace', the sequences of the temporary
>> tables remain on the 'main_tablespace'.
>>
>> i hope that will help making postgres even better :)
>>
>
> Did you ever try re-writing some of the temp table usage to use
> subselect's/views/cte/etc?

Yeah -- especially CTE.  But, assuming you really do need to keep a
temp table organized and you want absolutely minimum latency in the
temp table manipulating function, you can use a nifty trick so
organize a table around txid_current();

CREATE UNLOGGED TABLE Cache (txid BIGINT DEFAULT txid_current(), a
TEXT, b TEXT);
CREATE INDEX ON Cache(txid);
-- or --
CREATE INDEX ON Cache(txid, a); -- if a is lookup key etc.

When you insert to the table let the default catch the current txid
and make sure that all queries are properly filtering the table on
txid, and that all indexes are left prefixed on txid.

Why do this? Now the record delete operations can be delegated to an
external process.  At any time, a scheduled process can do:
DELETE from Cache;

This is not guaranteed to be faster, but it probably will be.

merlin

pgsql-performance by date:

Previous
From: Craig Ringer
Date:
Subject: Re: index-only scan is missing the INCLUDE feature
Next
From: Steve Crawford
Date:
Subject: Re: pgbouncer - massive overhead?