Greetings! Table definitions were included in the original post. I can repost (or send directly) if desired. I am using <inherits> to implement database partitioning based on the day. Postgresql 8.0.1 (and previous) has a number of issues when it comes to very large tables. Currently we have anywhere from 4,000,000 to 10,000,000 rows per day to deal with. With a <vacuum> running, we can't seem to cancel it until it finishes (which takes a bit of time when you have over 3 months of data). Insert/search performance also degrades with one large table (this gets into the issues of dealing with large volumes of time series data - really wish Postgresql would have the concept of a queue table where rows are always inserted at the end, and deletes only happen at the beginning, with block allocation). By using <inherits>, we can truncate a days worth of data very quickly, and only vacuum changing tables. Hopefully, the use of constraints in the query optimizer will make it into 8.1, so it will help some of our queries. I could hand optimize queries, but then I am essentially implementing an optimizer in our application code, which is definitely the wrong place to put it (also, if I was to go the full way, then I might switch to a database that supports time series data better, but would lose the ad-hoc query abilities of SQL). Indexes are on all the children (as per the post). in addition, when I use child tables directly, the indexes are used in the queries (which is one of the reasons why I suspect a bug related to pushing optimization information through <inherits>). Note that I also posted a followup that showed how a 1 row set would not use indexes when going through <inherits> whereas a simple = would. Regards! Ed On Wed, 3 Aug 2005, Dr NoName wrote: > post your table definitions. I suspect you are > indexing the parent table but not the children. > > btw, we tried using inherited tables in our > application and quickly found out that they are more > trouble then they are worth (at least the way they are > implemented in postgresql). There are other, more > portable ways of mapping a class hierarchy to > table(s). A few techniques are described in Fowler's > Patterns of Enterprise Application Architecture. > > hope this helps, > > Eugene > > > --- Edmund Dengler <edmundd@xxxxxxxxxxxx> wrote: > > > Greetings! > > > > I have already increased the stats from 10 to 100. > > In addition, if I > > specify individual tables, then the indexes are > > used. However, when I go > > through the <inherits>, then indexes are not used. I > > will try and expand > > the statistics, but suspect it is not the root cause > > of the problem. > > > > Regards! > > Ed > > > > > > On Tue, 2 Aug 2005, Scott Marlowe wrote: > > > > > On Tue, 2005-08-02 at 16:06, Dr NoName wrote: > > > > The solution to my problem was to increase > > statistics > > > > value and do another analyze. You can also > > change > > > > default_statistics_target parameter in > > > > postgresql.conf. Don't know if that's related to > > the > > > > problem you're seeing, but it's worth a try. > > > > > > Cool postgresql trick: > > > > > > alter database test set > > default_statistics_target=200; > > > > > > You can change the default for a databases's new > > tables too. > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 2: Don't 'kill -9' the postmaster > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
