Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    26

    Unanswered: Table partitioning made the query replies slower

    Hi all,

    I created an empty table UIP_ADAKENARI which as around 590.000 records and 15 sub-tables INHERITS the master table. All tables are only indexed using OBJECTID as my query structure is not available for other indexing.

    Before the partitioning the query of

    Code:
    Select * from "UIP_ADAKENARI" where (("CURX" < 38.0 AND "CURY" <= 34.0 AND "CLLX" >= 36.0 AND "CLLY" > 30.0))
    was taking app. 7 secs to response. I deleted all records on the table, created the table partitioning and re-entered the records to the table. All records divided into the right sub-tables.

    But when I re-send the query I am getting only 5.6 secs of query time which seems strange to me. I was expecting PostgreSQL goes directly to the related sub-table and get the results a lot faster.


    When I add Explain on my query I get a plan of:

    Code:
    "Result  (cost=0.00..37609.58 rows=28776 width=678)"
    "  ->  Append  (cost=0.00..37609.58 rows=28776 width=678)"
    "        ->  Seq Scan on "UIP_ADAKENARI"  (cost=0.00..12.20 rows=1 width=666)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_2630_4042" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_2630_3840" "UIP_ADAKENARI"  (cost=0.00..490.22 rows=1 width=678)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_2630_3638" "UIP_ADAKENARI"  (cost=0.00..15987.98 rows=24 width=675)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3034_4042" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3034_3840" "UIP_ADAKENARI"  (cost=0.00..16667.04 rows=25 width=670)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3034_3638" "UIP_ADAKENARI"  (cost=0.00..1883.34 rows=28706 width=669)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3438_4042" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3438_3840" "UIP_ADAKENARI"  (cost=0.00..749.64 rows=1 width=674)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3438_3638" "UIP_ADAKENARI"  (cost=0.00..551.04 rows=1 width=669)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3842_4042" "UIP_ADAKENARI"  (cost=0.00..1164.12 rows=1 width=668)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3842_3840" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_3842_3638" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_4245_4042" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_4245_3840" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    "        ->  Seq Scan on "UIP_ADAKENARI_4245_3638" "UIP_ADAKENARI"  (cost=0.00..13.00 rows=2 width=489)"
    "              Filter: (("CURX" < 38::double precision) AND ("CURY" <= 34::double precision) AND ("CLLX" >= 36::double precision) AND ("CLLY" > 30::double precision))"
    which I am not sure if it is right one.

    I set the constraint_exclusion on from postgresql.conf and restarted the server. No changes.

    Any other ideas?

    Many thanks.
    telmessos

  2. #2
    Join Date
    Jan 2010
    Posts
    26
    As an update "constraint_exclusion" on and off mode should bring different "explain" results. but does not matter if I set "constraint_exclusion" on or off, the results come the same.

    Forgot to mention about the version. The version of PostgreSQL is 8.3
    Last edited by telmessos; 01-02-12 at 11:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •