Is this partitioning like the schemas mentioned here:
http://www.postgresql.org/docs/current/static/ddl-schemas.html? Would those
help and increase performance?
/B
----- Original Message -----
From: "Jean-Luc Lachance" <jllachan@nsd.ca>
To: "Vivek Khera" <khera@kcilink.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, October 10, 2003 14:23
Subject: Re: [GENERAL] Table partitioning for maximum speed?
> BULL.
>
> How many times does PG have to scan the whole table because of MVCC?
> At least with partitioning there is a fighting chance that that won't be
> necessary.
> Queries that involve the field on which the table is partitioned execute
> faster by an order of magnitude.
> It also helps with vaccuming as PG can vaccum only one partition at a
> time.
> I have 17M row table where all records get frequently updated over a
> year.
> I would do my own partitioning with inheritance if it was not broken.
> Partitioning would be a BIG plus in my book. So would visibility of
> records but that is another fight.
>
> JLL
>
> Vivek Khera wrote:
> >
> > >>>>> "JB" == Jeff Boes <jboes@nexcerpt.com> writes:
> >
> > JB> Will a query against a table of 0.5 million rows beat a query
against
> > JB> a table of 7 million rows by a margin that makes it worth the hassle
> > JB> of supporting 15 "extra" tables?
> >
> > I think you'll be better off with a single table, as you won't have
> > contention for the index pages in the cache.
> >
> > One thing to do is to reindex reasonably often (for PG < 7.4) to avoid
> > index bloat, which will make them not fit in cache. Just check the
> > size of your index in the pg_class table, and when it gets big,
> > reindex (assuming you do lots of updates/inserts to the table).
> >
> > Your table splitting solution sounds like something I'd do if I were
> > forced to use mysql ;-)
> >
> > --
> > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> > Vivek Khera, Ph.D. Khera Communications, Inc.
> > Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
> > AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
your
> > joining column's datatypes do not match
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
