table partitioning and plpgsql functions in 8.2.3

From: "paul rivers" <privers(at)berkeley(dot)edu>
To: "'pgsql general'" <pgsql-general(at)postgresql(dot)org>
Subject: table partitioning and plpgsql functions in 8.2.3
Date: 2007-04-08 21:40:01
Message-ID: 000401c77a26$76819c20$23d09888@parzifal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Overview:

plpgsql functions seem to ignore partitioning, even with
constraint_exclusion on.

Description:

Version is 8.2.3 on RHEL 4, constraint_exlusion is on. I have an
events table (fw_events) partitioned by an int and a date (fw_id,
fw_date for discussion) following the recommendations outlined in
5.9 of the manual.

FWIW, each partition holds around 1M rows. There are presently
about 250 partitions (2 ids, around 4+ months of dates).

explain select count(*) from fw_events where fw_id = 1 and
fw_date = '2007-04-08' shows that the single partition table is
examined, and results are snappy when executed.

I created a function to do the same count, and it took orders of
magnitude longer.

I then created a plpgsql function to return the explain plan
instead, which seemed to indicate the plpgsql function scans all
the partitions.

Shouldn't the plpgsql function honor the partitioning and only
examine the single partition in the above example?

Thanks in advance,
Paul

Browse pgsql-general by date

  From Date Subject
Next Message paul rivers 2007-04-08 22:02:16 Re: table partitioning and plpgsql functions in 8.2.3
Previous Message Björn Lundin 2007-04-08 21:37:26 Re: Documenting PGSQL database.