Re: [GENERAL] Partitioned TEMP tables

[Ed Behn]

I tried that. I didn't seem to help.


*Ed Behn */ Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: (410)266-4426 / Cell: (240)696-7443
ed.b...@rockwellcollins.com

www.rockwellcollins.com


On Mon, Jul 31, 2017 at 4:16 PM, Tom Lane  wrote:

> Ed Behn  writes:
> > Does partitioning of TEMP tables not work like non-TEMP tables?
>
> Should be the same ... but you don't get any auto-analyze support on
> a temp table.  I wonder if you're remembering to ANALYZE the temp
> tables after you've populated them.
>
> regards, tom lane
>

Re: [GENERAL] Partitioned TEMP tables

[Tom Lane]

Ed Behn  writes:
> I tried that. I didn't seem to help.

Well, it works in a simple test case for me.  You'll need to post a
self-contained example that's not working if you want much help ...

regression=# create temp table pp(f1 int, f2 text);
CREATE TABLE
regression=# create temp table c1(check(f1 between 0 and 1)) inherits(pp);
CREATE TABLE
regression=# create temp table c2(check(f1 between 1 and 2)) inherits(pp);
CREATE TABLE
regression=# explain select * from pp where f1 < 1;
 QUERY PLAN 

 Append  (cost=0.00..25.88 rows=424 width=36)
   ->  Seq Scan on pp  (cost=0.00..0.00 rows=1 width=36)
 Filter: (f1 < 1)
   ->  Seq Scan on c1  (cost=0.00..25.88 rows=423 width=36)
 Filter: (f1 < 1)
(5 rows)

regression=# explain select * from pp where f1 > 1;
 QUERY PLAN 

 Append  (cost=0.00..25.88 rows=424 width=36)
   ->  Seq Scan on pp  (cost=0.00..0.00 rows=1 width=36)
 Filter: (f1 > 1)
   ->  Seq Scan on c2  (cost=0.00..25.88 rows=423 width=36)
 Filter: (f1 > 1)
(5 rows)


regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitioned TEMP tables

[Tom Lane]

Ed Behn  writes:
> Does partitioning of TEMP tables not work like non-TEMP tables?

Should be the same ... but you don't get any auto-analyze support on
a temp table.  I wonder if you're remembering to ANALYZE the temp
tables after you've populated them.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioned TEMP tables

[Ed Behn]

I have an issue regarding partitioned TEMP tables.

I have a database with a number of families of tables partitioned by day as
described in section 5.10 of the User's Manual. I have an empty parent
tables each with a number of child tables containing data partitioned by
date. Each child has a CHECK condition on the date of the data. This works
fine when I execute a SELECT statement against the parent table and specify
a value for the date.

However, I am currently working on a system that requires me to create a
family of TEMP tables with the same setup. So, I have an empty TEMP parent
with each day’s data in a TEMP child.

The thing is that if I try try to run SELECT against the parent table with
the date specified in the WHERE clause, I get terrible performance despite
the fact that an EXPLAIN of the query looks fine. (The query can run for an
hour and I finally give up and kill it.) However, if I specify the child
table directly in the FROM clause, the query runs fine. (It only takes a
few minutes.)

Does partitioning of TEMP tables not work like non-TEMP tables? In the same
query, I access the parent table of a non-TEMP family and that doesn’t
cause problems.

Any idea what’s going on here?
-Ed



*Ed Behn */ Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: (410)266-4426 / Cell: (240)696-7443
ed.b...@rockwellcollins.com

www.rockwellcollins.com