BUG #11442: Long binding time for queries on tables with partitions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      11442
Logged by:          Hooman Mozaffari
Email address:      hmozaffari@hubhead.com
PostgreSQL version: 9.3.5
Operating system:   Windows 7
Description:

Issue:
------
    Long binding time for queries on tables with partitions

Environment:
------------
    -PostgreSQL Database Server 9.3.5, compiled by Visual C++ build 1600,
64-bit – Windows 7
    -PostgreSQL JDBC: postgresql-9.3-1101-jdbc4.jar
    -Configuration: constraint_exclusion = 'partition'

Description:
------------
    Two identical tables with 1000 records one with 100 partitions and the
other one with no partition. Binding stage of queries on portioned table
takes almost 90 times more time.

Steps to reproduce:
-------------------
    1-Created table “test_table_with_partition” with 100 partitions and
“test_table_without_partition” table with no partition. Inserted 1000
records in both tables. Script bellow creates the tables and partitions:
(Please note the trigger for dispatching request have not been created since
we focused only on retrieval not inserting records in to partitioned
tables.)

        DO $$
        BEGIN
        CREATE TABLE test_table_with_partition(
            id Serial NOT NULL,
            name Character varying(128) NOT NULL,
            category_id Integer NOT NULL
        );

        CREATE TABLE test_table_without_partition(
            id Serial NOT NULL,
            name Character varying(128) NOT NULL,
            category_id Integer NOT NULL
        );
        CREATE INDEX test_table_without_partition_idx ON
test_table_without_partition (category_id);

        FOR i IN 1..100 LOOP
            EXECUTE 'CREATE TABLE test_table_partition_'||i||' ( CHECK ( category_id
= '||i||' ) ) INHERITS (test_table_with_partition)';
            EXECUTE 'CREATE INDEX test_table_partition_idx_'||i||' ON
test_table_partition_'||i||' (category_id)';
        END LOOP;

        FOR i IN 1..1000 LOOP
            EXECUTE 'INSERT INTO test_table_partition_1(name, category_id)
VALUES('||i||',1)';
            EXECUTE 'INSERT INTO test_table_without_partition(name, category_id)
VALUES('||i||',1)';
        END LOOP;
        END$$ LANGUAGE plpgsql;

    2-From a Java application using JDBC executed the following queries using
prepared statement and binding variables:
        SELECT * FROM test_table_with_partition    WHERE category_id = 1 and id =
1 and name = '1';
        SELECT * FROM test_table_wihtout_partition WHERE category_id = 1 and id =
1 and name = '1';

    3-Turned on the logs on PostgreSQL. The binding stage takes 1 ms for
none-partitioned table and 89 ms for partitioned table. Retried the
experiment many times and got same result.

        2014-09-17 09:41:08 EDT LOG:  duration: 1.000 ms  parse <unnamed>: SELECT
* FROM test_table_with_partition WHERE category_id = $1 and id = $2 and name
= $3
        2014-09-17 09:41:08 EDT LOG:  duration: 89.000 ms  bind <unnamed>: SELECT
* FROM test_table_with_partition WHERE category_id = $1 and id = $2 and name
= $3
        2014-09-17 09:41:08 EDT DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '1'
        2014-09-17 09:41:08 EDT LOG:  duration: 1.000 ms  execute <unnamed>:
SELECT * FROM test_table_with_partition WHERE category_id = $1 and id = $2
and name = $3
        2014-09-17 09:41:08 EDT DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '1'
        2014-09-17 09:41:08 EDT LOG:  duration: 1.000 ms  parse <unnamed>: SELECT
* FROM test_table_without_partition WHERE category_id = $1 and id = $2 and
name = $3
        2014-09-17 09:41:08 EDT LOG:  duration: 1.000 ms  bind <unnamed>: SELECT *
FROM test_table_without_partition WHERE category_id = $1 and id = $2 and
name = $3
        2014-09-17 09:41:08 EDT DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '1'
        2014-09-17 09:41:08 EDT LOG:  duration: 0.000 ms  execute <unnamed>:
SELECT * FROM test_table_without_partition WHERE category_id = $1 and id =
$2 and name = $3
        2014-09-17 09:41:08 EDT DETAIL:  parameters: $1 = '1', $2 = '1', $3 = '1'