Got it solved!
The problem was one of two things,or maybe both. I had somehow gotten
over 15 million records into the master table and even though I
"deleted" them and run VACUUM ANALYZE over the table, they were still
taking up space in the table. Perhaps even just opening a table with
that much garbage in it is what was taken 30-40 seconds. So I made a
copy of the structure, blew away the original table, taking the bad
records out with it and then renamed the copy and used that as the
master table
Also, I found that some of the partition tables had a third index
besides the Primary Key index. This was an earlier unique index that I
was using before I learned how to add a primary key to an existing
table. So I cleaned up all the partition tables making sure that they
all had the exact same indexes and constraints set,.
Then I relinked everything and tried it with several queries. Wow!
Over 20 million records (so far), and now I can retrieve any one of
them in less than a second! It seems that partitioning is even better
than I expected.
Thanks for the help. Your comment, Steve, about the Planner being
finicky was the clue. KI guess it's intolerant of inconsistency.
Probably a good thing for a database.
On 2/2/2011 3:10 PM, Steve Crawford wrote:
On 02/02/2011 01:35 PM, Bill Thoen wrote:
Steve Crawford wrote:
On 02/02/2011 12:17 PM, Bill Thoen wrote:
I've got a large (and growing) database set up as a partitioned
database....
What is the setting of contstraint_exclusion?
http://www.postgresql.org/docs/9.0/static/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION
Cheers,
Steve
It's set to 'Partition'
That sounds good. Out of curiosity, what happens if you use an
explicit cast?:
...where 'co'::char(2)...
I've seen lots of cases where the planner doesn't use indexes when the
data-type differs sometimes even subtly. Might be the same for
constraint exclusion.
Cheers,
Steve
--
*Bill Thoen*
GISnet - www.gisnet.com
303-786-9961
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
