#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0

    Postgresql 9.1 Partition pruning is not working


    I have created a partition for Table A in PostgreSQL and created indexes on couple of columns. The partition is a day wise partition on a date column but the index is on another key column. On running the query, PostgreSQL is doing a full table scan instead of using the day wise partition. Could you kindly let me know how to avoid full table scans in PostgreSQL. Note that the same query in Oracle is using the correct partition.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    You need to supply:

    • the create table statements (base and partitions)
    • the create index statements
    • the select statement
    • the output of the explain analyze statement

    And make sure you format the SQL statements properly using [code] tags
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    You need to supply:

    • the create table statements (base and partitions)
    • the create index statements
    • the select statement
    • the output of the explain analyze statement

    And make sure you format the SQL statements properly using [code] tags
    Ok, I will provide you this details..
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    You need to supply:

    • the create table statements (base and partitions)
    • the create index statements
    • the select statement
    • the output of the explain analyze statement

    And make sure you format the SQL statements properly using [code] tags
    1. CREATE TABLE STATEMENT

    BASE TABLE
    CREATE TABLE TABL1
    (
    COL1 character varying(22),
    COL2 character varying(22),
    COL5 date NOT NULL,
    COL6 character varying(10) NOT NULL,
    COL7 integer
    );

    INHERITED TABLE
    CREATE TABLE TAB1_120501
    (
    CONSTRAINT TAB1_120501_COL5_check CHECK (COL5 = '2012-05-01'::date)
    )
    INHERITS (TAB1)
    CREATE TABLE TAB1_120502
    (
    CONSTRAINT TAB1_120502_COL5_check CHECK (COL5 = '2012-05-02'::date)
    )
    INHERITS (TAB1);

    CREATE TABLE TAB1_120503
    (
    CONSTRAINT TAB1_120503_COL5_check CHECK (COL5 = '2012-05-03'::date)
    )
    INHERITS (TAB1);

    CREATE TABLE TAB1_120504
    (
    CONSTRAINT TAB1_120504_COL5_check CHECK (COL5 = '2012-05-04'::date)
    )
    INHERITS (TAB1);

    2) CREATE INDEX STATEMENT
    CREATE INDEX indx_TAB1_120501 ON TAB1_120501 (COL1);
    CREATE INDEX indx_TAB1_120502 ON TAB1_120502 (COL1);
    CREATE INDEX indx_TAB1_120503 ON TAB1_120503 (COL1);
    CREATE INDEX indx_TAB1_120504 ON TAB1_120504 (COL1);

    CREATE INDEX indx_TAB1_120501_dt ON TAB1_120501 (COL5);
    CREATE INDEX indx_TAB1_120502_dt ON TAB1_120502 (COL5);
    CREATE INDEX indx_TAB1_120503_dt ON TAB1_120503 (COL5);
    CREATE INDEX indx_TAB1_120504_dt ON TAB1_120504 (COL5);

    3 ) SELECT STETEMENT
    SELECT * FROM
    (select col1,t2.col2 , T2.Col_type, count(col1)
    from tabl1 t1,
    tabl2 t2
    WHERE T1.COL1 = T2.COL1
    AND COL3 = 'TT'
    AND COL4 = 50
    AND CAL5 = TO_DATE(P_CUR_DATE,'DD/MM/YYYY')
    GROUP BY COL1,T2.COL2 ,t2.col_type) T1,
    TABLE2 T2
    WHERE T1.col_type = t2.col_type
    AND T2.COL5 = 'B'
    AND T1.COL_INT = T2.COL_INT;

IMN logo majestic logo threadwatch logo seochat tools logo