Re: Table partitioning for maximum speed? - Mailing list pgsql-general

No.  There is a big difference between schemas and partitioned tables.

The closest thing would be a bunch of tables enherited from a base
table.
Each tables having a common field all the same for a specific table.

The problem is that the planner cannot be aware of this and make better
use of the implicit key.

JLL


David Busby wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org