Re: Failure to use indexes (fwd) - Mailing list pgsql-general

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@eSentire.com> 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@postgresql.org so that your
>        message can get through to the mailing list cleanly
>