From: Ildar Musin <i.musin@postgrespro.ru>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Declarative partitioning
Date: Fri, 20 May 2016 18:29:00 +0300
Message-ID: <573F2D3C.8010404@postgrespro.ru> (raw)
In-Reply-To: <573ECCC3.7080104@lab.ntt.co.jp>
References: <55D3093C.5010800@lab.ntt.co.jp>
<CAPpHfdut34uqoY1vKgvBhsGZqhR6Nky=_Dxn0un57G_M2CvLTA@mail.gmail.com>
<CA+HiwqFFH10_ku6z_Ps=H7=pR5cWJk9Bdr9nE08+3F53yTEr9A@mail.gmail.com>
<CAPpHfdu0mGbt9T2Jx04OKJfJQ0zW55BFqmisinRYWd2OaowRWQ@mail.gmail.com>
<CA+TgmoaoZRqk2dr3xonta3o4omKuO4V4niVC2DKKo9VpdMc_zQ@mail.gmail.com>
<56F0AC96.2010303@lab.ntt.co.jp>
<CAFjFpReZ2nmpfOA2uGdgVp3ivWC13VDFYOQ7rs5LFeqo_Zm4vA@mail.gmail.com>
<57106F9E.3000309@lab.ntt.co.jp>
<57163954.2020701@postgrespro.ru>
<CAPpHfdtH4rhckHzkqGhGP8O7aejj=J7_qJRA3dTu+iAUpuzPgg@mail.gmail.com>
<CA+HiwqEMQ2EZ0GKBjg2hHT1uP-NRodtodc2JjN1vd0xp_BzTCg@mail.gmail.com>
<571759D8.7030205@lab.ntt.co.jp>
<57179653.30905@postgrespro.ru>
<CA+HiwqF=0v6afwGj=Fc3gBF94Wp65naJUiDt6sFQiNXvtsFXSg@mail.gmail.com>
<57183572.8020900@lab.ntt.co.jp>
<5739C742.80504@postgrespro.ru>
<573ACEB2.1090705@lab.ntt.c! o.jp>
<1404.1463505762@sss.pgh.pa.us>
<573BC4DB.2040507@lab.ntt.co.jp>
<573C8BFB.7050707@postgrespro.ru>
<573ECCC3.7080104@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Hi Amit,
On 20.05.2016 11:37, Amit Langote wrote:
> Perhaps you're already aware but may I also suggest looking at how clauses
> are matched to indexes? For example, consider how
> match_clauses_to_index() in src/backend/optimizer/path/indxpath.c works.
Thanks, I'll take a closer look at it.
> Moreover, instead of pruning partitions in planner prep phase, might it
> not be better to do that when considering paths for the (partitioned) rel?
> IOW, instead of looking at parse->jointree, we should rather be working
> with rel->baserestrictinfo. Although, that would require some revisions
> to how append_rel_list, simple_rel_list, etc. are constructed and
> manipulated in a given planner invocation. Maybe it's time for that...
> Again, you may have already considered these things.
>
Yes, you're right, this is how we did it in pg_pathman extension. But
for this patch it requires further consideration and I'll do it in future!
> Could you try with the attached updated set of patches? I changed
> partition descriptor relcache code to eliminate excessive copying in
> previous versions.
>
> Thanks,
> Amit
I tried your new patch and got following results, which are quite close
to the ones using pointer to PartitionDesc structure (TPS):
# of partitions | single row | single partition
----------------+------------+------------------
100 | 3014 | 1024
1000 | 2964 | 1001
2000 | 2874 | 1000
However I've encountered a problem which is that postgres crashes
occasionally while creating partitions. Here is function that reproduces
this behaviour:
CREATE OR REPLACE FUNCTION fail()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
DROP TABLE IF EXISTS abc CASCADE;
CREATE TABLE abc (id SERIAL NOT NULL, a INT, dt TIMESTAMP) PARTITION BY
RANGE (a);
CREATE INDEX ON abc (a);
CREATE TABLE abc_0 PARTITION OF abc FOR VALUES START (0) END (1000);
CREATE TABLE abc_1 PARTITION OF abc FOR VALUES START (1000) END (2000);
END
$$;
SELECT fail();
It happens not every time but quite often. It doesn't happen if I
execute this commands one by one in psql. Backtrace:
#0 range_overlaps_existing_partition (key=0x7f1097504410,
range_spec=0x1d0f400, pdesc=0x1d32200, with=0x7ffe437ead00) at
partition.c:747
#1 0x000000000054c2a5 in StorePartitionBound (relid=245775,
parentId=245770, bound=0x1d0f400) at partition.c:578
#2 0x000000000061bfc4 in DefineRelation (stmt=0x1d0dfe0, relkind=114
'r', ownerId=10, typaddress=0x0) at tablecmds.c:739
#3 0x00000000007f4473 in ProcessUtilitySlow (parsetree=0x1d1a150,
queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES
START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0,
dest=0xdb5ca0 <spi_printtupDR>, completionTag=0x7ffe437eb500 "")
at utility.c:983
#4 0x00000000007f425e in standard_ProcessUtility (parsetree=0x1d1a150,
queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES
START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0,
dest=0xdb5ca0 <spi_printtupDR>,
completionTag=0x7ffe437eb500 "") at utility.c:907
#5 0x00000000007f3354 in ProcessUtility (parsetree=0x1d1a150,
queryString=0x1d1d940 "CREATE TABLE abc_0 PARTITION OF abc FOR VALUES
START (0) END (1000)", context=PROCESS_UTILITY_QUERY, params=0x0,
dest=0xdb5ca0 <spi_printtupDR>, completionTag=0x7ffe437eb500 "")
at utility.c:336
#6 0x000000000069f8b2 in _SPI_execute_plan (plan=0x1d19cf0,
paramLI=0x0, snapshot=0x0, crosscheck_snapshot=0x0, read_only=0 '\000',
fire_triggers=1 '\001', tcount=0) at spi.c:2200
#7 0x000000000069c735 in SPI_execute_plan_with_paramlist
(plan=0x1d19cf0, params=0x0, read_only=0 '\000', tcount=0) at spi.c:450
#8 0x00007f108cc6266f in exec_stmt_execsql (estate=0x7ffe437eb8e0,
stmt=0x1d05318) at pl_exec.c:3517
#9 0x00007f108cc5e5fc in exec_stmt (estate=0x7ffe437eb8e0,
stmt=0x1d05318) at pl_exec.c:1503
#10 0x00007f108cc5e318 in exec_stmts (estate=0x7ffe437eb8e0,
stmts=0x1d04c98) at pl_exec.c:1398
#11 0x00007f108cc5e1af in exec_stmt_block (estate=0x7ffe437eb8e0,
block=0x1d055e0) at pl_exec.c:1336
#12 0x00007f108cc5c35d in plpgsql_exec_function (func=0x1cc2a90,
fcinfo=0x1cf7f50, simple_eval_estate=0x0) at pl_exec.c:434
...
Thanks
--
Ildar Musin
i.musin@postgrespro.ru
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: i.musin@postgrespro.ru, Langote_Amit_f8@lab.ntt.co.jp, tgl@sss.pgh.pa.us
Subject: Re: Declarative partitioning
In-Reply-To: <573F2D3C.8010404@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox