Re: [GENERAL] Partitioned TEMP tables
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 <t...@sss.pgh.pa.us> wrote: > Ed Behn <ed.b...@rockwellcollins.com> 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 >
[GENERAL] Partitioned TEMP tables
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
[GENERAL] Partitioned Data and Locking
I've found what to me is a surprising locking behavior when querying partitioned data as described in section 5.10 of the User's Manual. I have an empty parent table with a number of child tables containing data. Each child has a CHECK condition on the relevant column. I am executing a SELECT query against the parent table with a condition on the column in the CHECK in the WHERE clause. I have constraint_exclusion set to partition. If I run EXPLAIN on the query, I get a result that shows that only the child tables whose CHECKs are consistent with the WHERE clause are searched. This is exactly what I expected. However, when I run the query, AccessShareLocks are obtained by the transaction for all child tables (and their indices). Am I misunderstanding something? I seems that these locks shouldn't exist if the query plan doesn't use most of the child tables. If this is a bug, perhaps it could be fixed in a future release. I would be beneficial to my application, as we most often are only writing to one partition in any given day. If a query against older data is running, the write transaction could still proceed. -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
