Why does a normally fast query run so slow when the table is in a partition? - Mailing list pgsql-general
| From | Bill Thoen |
|---|---|
| Subject | Why does a normally fast query run so slow when the table is in a partition? |
| Date | |
| Msg-id | 4D49BBD8.8030201@gisnet.com Whole thread Raw |
| Responses |
Re: Why does a normally fast query run so slow when the
table is in a partition?
|
| List | pgsql-general |
I've got a large (and growing) database set up as a partitioned database. The partitions are physically broken out by state plus a unique id for each. There's roughly 20 million records in the whole thing just now. My question is, why does a simple query supplying both parts of the index key work nearly instantly as expected when I submit it to the appropriate partition table directly, but the same query when sent to the master table takes nearly 3/4 of a minute to return one record? Actually, running the queries with 'Explain analyze verbose' tells me what it chose, so I know it's slopw because it chose to do a sequential scan on the master table but what I'd like to know is why does it take so long to go through the master table looking for the partition for 'co'? According to the log, if I read it correctly, it took nearly 40 seconds just to scan through the 19 partition tables before it found the colorado partition. Can soeone tell me ho wot speed up that step?
Also (this might be relevant) I accidentally got 15 million records into the master table earlier, but those have all been deleted and I've run VACUUM ANALYZE on the master table since then.
The following shows the details and the environment. I'm using PostgreSQL 8.4.5 and running on CentOS 5.5
This is the master table. It has no records or indexes as per the PG manual.
fsa=# \d clu
Table "vfm.clu"
Column | Type | Modifiers
-------------+---------------+-----------
ogc_fid | bigint | not null
geom | geometry |
comments | character(80) |
statecd | character(2) |
countycd | character(3) |
tractnbr | character(7) |
farmnbr | character(7) |
clunbr | numeric(7,0) |
acres | numeric(8,2) |
fsa_acres | numeric(8,2) |
heltypecd | character(1) |
cluclscd | numeric(2,0) |
cluid | character(36) |
admnstate | character(2) |
admncounty | character(3) |
source_disc | character(2) | not null
This is one of the partition tables. It has the same structure, although the key fields are not in the same order as the master table. It is also indexed on source_disc + ogc_fid (and spatially as well). Its constraint is that only records with 'co' in the source_disk attribute can be added or found here.
fsa=# \d clu_co
Table "vfm.clu_co"
Column | Type | Modifiers
-------------+---------------+-----------
geom | geometry |
comments | character(80) |
statecd | character(2) |
countycd | character(3) |
tractnbr | character(7) |
farmnbr | character(7) |
clunbr | numeric(7,0) |
acres | numeric(8,2) |
fsa_acres | numeric(8,2) |
heltypecd | character(1) |
cluclscd | numeric(2,0) |
cluid | character(36) |
admnstate | character(2) |
admncounty | character(3) |
ogc_fid | bigint | not null
source_disc | character(2) | not null
Indexes:
"clu_co_pkey" PRIMARY KEY, btree (source_disc, ogc_fid)
"clu_co_geom" gist (geom)
Check constraints:
"cd_id" CHECK (source_disc = 'co'::bpchar)
Inherits: clu
Here's the query that executes quickly in the partition table. Notice that it's using the index for a fast lookup.
fsa=# explain analyze verbose select :flds from clu_co where source_disc='co' and ogc_fid = 116337;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
-----------
Index Scan using clu_co_pkey on clu_co (cost=0.00..8.31 rows=1 width=48) (actual time=0.079..0.086 rows=
1 loops=1)
Output: source_disc, ogc_fid, statecd, countycd, tractnbr, farmnbr, clunbr, acres
Index Cond: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
Total runtime: 0.177 ms
(4 rows)
fsa=# select :flds from clu where source_disc='co' and ogc_fid = 116337;
source_disc | ogc_fid | statecd | countycd | tractnbr | farmnbr | clunbr | acres
-------------+---------+---------+----------+----------+---------+--------+---------
co | 116337 | 08 | 043 | 0000533 | 0000065 | 9 | 4677.79
(1 row)
The same query when sent through the master table. Notice it's using a sequential scan. But why does this operation take 38 seconds? How do I speed that up?
fsa=# explain analyze verbose select :flds from clu where source_disc='co' and ogc_fid = 116337;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Result (cost=0.00..1098364.31 rows=2 width=52) (actual time=38367.332..38367.355 rows=1 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
-> Append (cost=0.00..1098364.31 rows=2 width=52) (actual time=38367.325..38367.339 rows=1 loops=1)
-> Seq Scan on clu (cost=0.00..1098356.00 rows=1 width=57)
(actual time=38367.222..38367.222 rows=0 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
Filter: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
-> Index Scan using clu_co_pkey on clu_co clu (cost=0.00..8.31 rows=1 width=48)
(actual time=0.090..0.096 rows=1 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
Index Cond: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
Total runtime: 38367.450 ms
(10 rows)
TIA,
- Bill
Also (this might be relevant) I accidentally got 15 million records into the master table earlier, but those have all been deleted and I've run VACUUM ANALYZE on the master table since then.
The following shows the details and the environment. I'm using PostgreSQL 8.4.5 and running on CentOS 5.5
This is the master table. It has no records or indexes as per the PG manual.
fsa=# \d clu
Table "vfm.clu"
Column | Type | Modifiers
-------------+---------------+-----------
ogc_fid | bigint | not null
geom | geometry |
comments | character(80) |
statecd | character(2) |
countycd | character(3) |
tractnbr | character(7) |
farmnbr | character(7) |
clunbr | numeric(7,0) |
acres | numeric(8,2) |
fsa_acres | numeric(8,2) |
heltypecd | character(1) |
cluclscd | numeric(2,0) |
cluid | character(36) |
admnstate | character(2) |
admncounty | character(3) |
source_disc | character(2) | not null
This is one of the partition tables. It has the same structure, although the key fields are not in the same order as the master table. It is also indexed on source_disc + ogc_fid (and spatially as well). Its constraint is that only records with 'co' in the source_disk attribute can be added or found here.
fsa=# \d clu_co
Table "vfm.clu_co"
Column | Type | Modifiers
-------------+---------------+-----------
geom | geometry |
comments | character(80) |
statecd | character(2) |
countycd | character(3) |
tractnbr | character(7) |
farmnbr | character(7) |
clunbr | numeric(7,0) |
acres | numeric(8,2) |
fsa_acres | numeric(8,2) |
heltypecd | character(1) |
cluclscd | numeric(2,0) |
cluid | character(36) |
admnstate | character(2) |
admncounty | character(3) |
ogc_fid | bigint | not null
source_disc | character(2) | not null
Indexes:
"clu_co_pkey" PRIMARY KEY, btree (source_disc, ogc_fid)
"clu_co_geom" gist (geom)
Check constraints:
"cd_id" CHECK (source_disc = 'co'::bpchar)
Inherits: clu
Here's the query that executes quickly in the partition table. Notice that it's using the index for a fast lookup.
fsa=# explain analyze verbose select :flds from clu_co where source_disc='co' and ogc_fid = 116337;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
-----------
Index Scan using clu_co_pkey on clu_co (cost=0.00..8.31 rows=1 width=48) (actual time=0.079..0.086 rows=
1 loops=1)
Output: source_disc, ogc_fid, statecd, countycd, tractnbr, farmnbr, clunbr, acres
Index Cond: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
Total runtime: 0.177 ms
(4 rows)
fsa=# select :flds from clu where source_disc='co' and ogc_fid = 116337;
source_disc | ogc_fid | statecd | countycd | tractnbr | farmnbr | clunbr | acres
-------------+---------+---------+----------+----------+---------+--------+---------
co | 116337 | 08 | 043 | 0000533 | 0000065 | 9 | 4677.79
(1 row)
The same query when sent through the master table. Notice it's using a sequential scan. But why does this operation take 38 seconds? How do I speed that up?
fsa=# explain analyze verbose select :flds from clu where source_disc='co' and ogc_fid = 116337;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Result (cost=0.00..1098364.31 rows=2 width=52) (actual time=38367.332..38367.355 rows=1 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
-> Append (cost=0.00..1098364.31 rows=2 width=52) (actual time=38367.325..38367.339 rows=1 loops=1)
-> Seq Scan on clu (cost=0.00..1098356.00 rows=1 width=57)
(actual time=38367.222..38367.222 rows=0 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
Filter: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
-> Index Scan using clu_co_pkey on clu_co clu (cost=0.00..8.31 rows=1 width=48)
(actual time=0.090..0.096 rows=1 loops=1)
Output: vfm.clu.source_disc, vfm.clu.ogc_fid, vfm.clu.statecd, vfm.clu.countycd,
vfm.clu.tractnbr, vfm.clu.farmnbr, vfm.clu.clunbr, vfm.clu.acres
Index Cond: ((source_disc = 'co'::bpchar) AND (ogc_fid = 116337))
Total runtime: 38367.450 ms
(10 rows)
TIA,
- Bill
--
Bill Thoen
GISnet - www.gisnet.com
1401 Walnut St., Suite C
Boulder, CO 80302
303-786-9961 tel
303-443-4856 fax
bthoen@gisnet.com
pgsql-general by date: