Re: Table partitioning for maximum speed?

Jean-Luc Lachance wrote:
>
> 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.

I meant to say visibility of record in the index.

>
> JLL
>
> Vivek Khera wrote:
> >
> > >>>>> "JB" == Jeff Boes <jboes(at)nexcerpt(dot)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(at)kciLink(dot)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