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

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