Table partitioning made the query replies slower

telmessos · #1 (**permalink**) 01-02-12, 07:40

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

telmessos · #2 (**permalink**) 01-02-12, 10:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech