Обсуждение: Why does a normally fast query run so slow when the table is in a partition?

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

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

On 02/03/2011 07:29 AM, Bill Thoen wrote:
> Got it solved!
>
Great.
> 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.

If you want to delete every record in the table, use truncate (truncate
table only tablename;), it is far faster than delete all and clears out
the unused space.

"Vacuum analyze" just does a vacuum and an analyze. Vacuum does not
reclaim space but merely identifies "holes" in the table that can be
reused. "Vacuum full" will reclaim the space but is very slow. "Cluster"
is almost always the preferred way of reclaiming space.

Both "vacuum full" and "cluster" require an exclusive lock.

Cheers,
Steve