From: | Matthieu Huin <matthieu(dot)huin(at)wallix(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | temporary table as a subset of an existing table and indexes |
Date: | 2010-11-08 17:15:31 |
Message-ID: | 4CD83033.2020309@wallix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greetings all,
I am trying to optimize SELECT queries on a large table (10M rows and
more) by using temporary tables that are subsets of my main table, thus
narrowing the search space to a more manageable size.
Is it possible to transfer indices (or at least use the information from
existing indices) from the big table to its subset in a reasonable
amount of time ?
When I try :
CREATE TEMPORARY TABLE tmp AS
SELECT * FROM big_table WHERE condition;
The table creation is fast ( a few seconds ) as there are indices on the
big table that are optimized for condition, but then indexing the data
is rather costly (the new table would have around 100k rows) and takes a
few minutes to complete. This is not acceptable as the whole process
aims at reducing the query time.
I get even worse results with the following transaction :
CREATE TEMPORARY TABLE tmp ( LIKE big_table INCLUDING INDEXES );
INSERT INTO tmp SELECT * FROM big_table WHERE condition;
Also, partitioning my big table from the very beginning is not an
option, as it doesn't guarantee index key unicity ( according to
http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html ).
Any suggestions on this ?
Kind regards,
Matthieu Huin
From | Date | Subject | |
---|---|---|---|
Next Message | Jakub Ouhrabka | 2010-11-08 18:19:43 | ERROR: Out of memory - when connecting to database |
Previous Message | Tom Lane | 2010-11-08 17:09:39 | Re: Syntax of: alter table ... add constraint ... |