Re: [HACKERS] path toward faster partition pruning
От | Beena Emerson |
---|---|
Тема | Re: [HACKERS] path toward faster partition pruning |
Дата | |
Msg-id | CAOG9ApFJ2q0mOve_V5yvrCeSGwUo6orL54kWPyCWVpY_G+MVnQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] path toward faster partition pruning (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Ответы |
Re: [HACKERS] path toward faster partition pruning
(Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
|
Список | pgsql-hackers |
Hello Amit, Thanks for the updated patches On Wed, Oct 25, 2017 at 1:07 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: > On 2017/10/25 15:47, Amit Langote wrote: >> On 2017/10/24 1:38, Beena Emerson wrote: >>> I had noticed this and also that this crash: >>> >>> tprt PARTITION BY RANGE(Col1) >>> tprt_1 FOR VALUES FROM (1) TO (50001) PARTITION BY RANGE(Col1) >>> tprt_11 FOR VALUES FROM (1) TO (10000), >>> tprt_1d DEFAULT >>> tprt_2 FOR VALUES FROM (50001) TO (100001) >>> >>> EXPLAIN (COSTS OFF) SELECT * FROM tprt WHERE col1 BETWEEN 20000 AND 70000; >>> server closed the connection unexpectedly >>> This probably means the server terminated abnormally >>> before or while processing the request. >>> The connection to the server was lost. Attempting reset: Failed. >>> !> >> >> ...and this (crash) were due to bugs in the 0005 patch. > > [ .... ] > >> Should be fixed in the attached updated version. > > Oops, not quite. The crash that Beena reported wasn't fixed (or rather > reintroduced by some unrelated change after once confirming it was fixed). > > Really fixed this time. > The crashes are fixed. However, handling of DEFAULT partition in various queries is not proper. Case 1: In this case default should be selected.DROP TABLE tprt; CREATE TABLE tprt (col1 int, col2 int) PARTITION BY range(col1);CREATE TABLE tprt_1 PARTITION OF tprt FOR VALUES FROM (1) TO (50001) PARTITION BY list(col1); CREATE TABLE tprt_11 PARTITION OF tprt_1 FOR VALUES IN (20000, 25000); CREATE TABLE tprt_12 PARTITIONOF tprt_1 FOR VALUES IN (50000, 35000); CREATE TABLE tprt_13 PARTITION OF tprt_1 FOR VALUES IN (10000); CREATE TABLEtprt_1d PARTITION OF tprt_1 DEFAULT; postgres=# EXPLAIN (COSTS OFF) SELECT * FROM tprt WHERE col1 < 10000; QUERY PLAN --------------------------Result One-Time Filter: false (2 rows) Case 2: In this case DEFAULT need not be selected. DROP TABLE tprt; CREATE TABLE tprt (col1 int, col2 int) PARTITION BY range(col1); CREATE TABLE tprt_1 PARTITION OF tprtFOR VALUES FROM (1) TO (50001) PARTITION BY range(col1); CREATE TABLE tprt_11 PARTITION OF tprt_1 FOR VALUES FROM (1) TO (10000); CREATE TABLE tprt_12 PARTITIONOF tprt_1 FOR VALUES FROM (10000) TO (20000); CREATE TABLE tprt_13 PARTITION OF tprt_1 FOR VALUES FROM (20000) TO(30000); CREATE TABLE tprt_1d PARTITION OF tprt_1 DEFAULT; INSERT INTO tprt SELECT generate_series(1,50000), generate_series(1,50000); postgres=# EXPLAIN (COSTS OFF) SELECT * FROM tprt WHERE col1 < 10000; QUERY PLAN --------------------------------Append -> Seq Scan on tprt_11 Filter: (col1 < 10000) -> Seq Scan on tprt_1d Filter: (col1 < 10000) (5 rows) -- Beena Emerson EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
В списке pgsql-hackers по дате отправления:
Следующее
От: Pavel StehuleДата:
Сообщение: Re: [HACKERS] pgbench - allow to store select results into variables