Why does a normally fast query run so slow when the table is in a partition?

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

--

*Bill Thoen*
GISnet - www.gisnet.com <http://www.gisnet.com/>
1401 Walnut St., Suite C
Boulder, CO 80302
303-786-9961 tel
303-443-4856 fax
bthoen(at)gisnet(dot)com