Thread: many tables vs large tables

What is the general consensus around here ... to have many smaller tables, or have a few large tables?

I'm looking at a db model where a client has around 5500 spatial (PostGIS) tables, where the volume of each one varies
greatly ... from a few hundred rows to over 250,000.

Speed is of the utmost importance. I'm investigating various options, like grouping the tables based on a common
attribute or spatial type (POINT, LINESTRING, etc) into many multi-million tuple tables.

Or, table inheritance could be my friend here, in terms of performance. Ie. Using inheritance and constraint exclusion,

the query planner could quickly isolate the tables of interest.  It's orders of magnitude faster to perform a
sequential 
scan through a relatively small table than it is to do an index scan on a large, likely unclustered table.  The
question 
is, can the underlying OS handle thousands of tables in a tablespace?  Would it overwhelm the parser to perform
constraint exclusion on 50-100 tables?  Can it be done relatively quickly?

Clearly, serious testing is in order, but I just wanted to get a feel for things before I dive in.

Cheers,
Kevin

Hello ,
I am trying to install postgresql-8.1.5 and postgresql-8.2.5 in linux (Linux
version 2.6.25-14.fc9.i686 (mockbuild@) (gcc version 4.3.0 20080428 (Red Hat
4.3.0-8) (GCC) ) #1 SMP Thu May 1 06:28:41 EDT 2008).but during compilation
it is showing following error.

make[3]: *** [plperl.o] Error 1
make[3]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl/plperl'
make[2]: *** [all] Error 1
make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src'
make: *** [all] Error 2

Please tell me how I can avoid this kind of error.

Thanks & regard
Praveen kumar.

 Hello ,
 I am trying to install postgresql-8.1.5 and postgresql-8.2.5 in linux
(Linux
 version 2.6.25-14.fc9.i686 (mockbuild@) (gcc version 4.3.0 20080428 (Red
Hat
 4.3.0-8) (GCC) ) #1 SMP Thu May 1 06:28:41 EDT 2008).but during compilation
 it is showing following error.

I configure with following options.
./configure --prefix=/home/local/pgsql/ --without-readline --with-perl --with-python
 --with-tcl --with-tclconfig=/usr/src/tcl8.4.16/unix --enable-nls
but when I execute command   "make " that time I got following errors.

 make[3]: *** [plperl.o] Error 1
 make[3]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl/plperl'
 make[2]: *** [all] Error 1
 make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl'
 make[1]: *** [all] Error 2
 make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src'
 make: *** [all] Error 2

 Please tell me how I can avoid this kind of error.

 Thanks & regard
 Praveen kumar.

Hello Tom,
During configure I find the error in config.log file
checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm
in @INC (@INC contains: /usr/lib/perl5/5.10.0/i386-linux-thread-multi
/usr/lib/perl5/5.10.0
/usr/lib/perl5/site_perl/5.10.0/i386-linux-thread-multi
/usr/lib/perl5/site_perl/5.10.0 /usr/lib/perl5/site_perl/5.8.8
/usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6
/usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl
/usr/lib/perl5/vendor_perl/5.10.0/i386-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.10.0 /usr/lib/perl5/vendor_perl/5.8.8
/usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6
/usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl .).

and when  I execute the "make" command that time I find the following
errors.

make -C error SUBSYS.o
make[4]: Entering directory
`/home/postgres/postgresql-8.1.5/src/backend/utils/error'
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement
 -Wendif-labels -fno-strict-aliasing -fpic -I. -I../../../src/include -D_GNU_SOURCE
  -I/usr/lib/perl5/5.10.0/i386-linux-thread-multi/CORE  -c -o plperl.o
plperl.c
plperl.c:67:20: error: EXTERN.h: No such file or directory
plperl.c:68:18: error: perl.h: No such file or directory
plperl.c:69:18: error: XSUB.h: No such file or directory
ppport.h:174:24: error: patchlevel.h: No such file or directory
ppport.h:177:44: error: could_not_find_Perl_patchlevel.h: No such file or
directory
ppport.h:371: error: expected â)â before â*â token
ppport.h:563: warning: type defaults to âintâ in declaration of âSVâ
ppport.h:563: error: expected â;â, â,â or â)â before â*â token
plperl.c:122: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:123: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:145: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:147: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:324: error: âplperl_proc_hashâ undeclared (first use in this
function)
plperl.c:374: error: âSVâ undeclared (first use in this function)
plperl.c:374: error: âresâ undeclared (first use in this function)
plperl.c:417: error: expected â)â before â*â token
plperl.c:451: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:480: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:576: error: expected â)â before â*â token
plperl.c:736: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:831: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
âvoidâ
plperl.c:832: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
âvoidâ
plperl.c:844: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:939: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â before
â*â token
plperl.c:1000: error: âSVâ undeclared (first use in this function)
plperl.c:1000: error: âperlretâ undeclared (first use in this function)
plperl.c:1001: warning: ISO C90 forbids mixed declarations and code
plperl.c:1003: error: âarray_retâ undeclared (first use in this function)
plperl.c:1032: warning: implicit declaration of function
âplperl_call_perl_funcâ
plperl.c:1051: warning: implicit declaration of function âSvTYPEâ
plperl.c:1051: error: âSVt_RVâ undeclared (first use in this function)
plperl.c:1052: warning: implicit declaration of function âSvRVâ
plperl.c:1052: error: âSVt_PVAVâ undeclared (first use in this function)
plperl.c:1055: error: âsvpâ undeclared (first use in this function)
plperl.c:1056: error: âAVâ undeclared (first use in this function)
plperl.c:1056: error: âravâ undeclared (first use in this function)
plperl.c:1056: error: expected expression before â)â token
plperl.c:1058: warning: implicit declaration of function âav_fetchâ
plperl.c:1060: warning: implicit declaration of function
âplperl_return_nextâ
plperl.c:1064: error: âSVt_NULLâ undeclared (first use in this function)
plperl.c:1095: warning: implicit declaration of function âSvOKâ
plperl.c:1096: error: âSVt_PVHVâ undeclared (first use in this function)
plperl.c:1114: warning: implicit declaration of function
âplperl_build_tuple_resultâ
plperl.c:1114: error: âHVâ undeclared (first use in this function)
plperl.c:1114: error: expected expression before â)â token
plperl.c:1114: warning: assignment makes pointer from integer without a cast
plperl.c:1122: warning: implicit declaration of function âSvROKâ
plperl.c:1125: warning: implicit declaration of function
âplperl_convert_to_pg_arrayâ
plperl.c:1126: warning: implicit declaration of function âSvREFCNT_decâ
plperl.c:1130: warning: implicit declaration of function âSvPVâ
plperl.c:1130: error: ânaâ undeclared (first use in this function)
plperl.c:1130: warning: assignment makes pointer from integer without a cast
plperl.c: In function âplperl_trigger_handlerâ:
plperl.c:1150: error: âSVâ undeclared (first use in this function)
plperl.c:1150: error: âperlretâ undeclared (first use in this function)
plperl.c:1151: warning: ISO C90 forbids mixed declarations and code
plperl.c:1152: error: âsvTDâ undeclared (first use in this function)
plperl.c:1153: error: âHVâ undeclared (first use in this function)
plperl.c:1153: error: âhvTDâ undeclared (first use in this function)
plperl.c:1172: error: expected expression before â)â token
plperl.c:1183: error: âSVt_NULLâ undeclared (first use in this function)
plperl.c:1202: error: ânaâ undeclared (first use in this function)
plperl.c:1253: error: âSVâ undeclared (first use in this function)
plperl.c:1253: error: âsvpâ undeclared (first use in this function)
plperl.c:1276: error: âplperl_proc_hashâ undeclared (first use in this
function)
plperl.c:1468: error: âplperl_proc_descâ has no member named âreferenceâ
plperl.c:1479: error: âIVâ undeclared (first use in this function)
plperl.c:1479: error: expected â)â before âprodescâ
plperl.c:1490: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â
before â*â token
plperl.c:1544: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â
before â*â token
plperl.c:1613: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â
before â*â token
plperl.c:1657: error: expected â)â before â*â token
plperl.c:1770: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â
before â*â token
plperl.c:1844: error: expected â=â, â,â, â;â, âasmâ or â__attribute__â
before â*â token




----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "praveen" <praveen.k@renaissance-it.com>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, November 04, 2008 8:19 PM
Subject: Re: Installation Error of postgresql-8.1.5 with perl.


> "praveen" <praveen.k@renaissance-it.com> writes:
>> but when I execute command   "make " that time I got following errors.
>
>>  make[3]: *** [plperl.o] Error 1
>>  make[3]: Leaving directory
>> `/home/postgres/postgresql-8.1.5/src/pl/plperl'
>>  make[2]: *** [all] Error 1
>>  make[2]: Leaving directory `/home/postgres/postgresql-8.1.5/src/pl'
>>  make[1]: *** [all] Error 2
>>  make[1]: Leaving directory `/home/postgres/postgresql-8.1.5/src'
>>  make: *** [all] Error 2
>
> 1. You've snipped away the actual error message, so no one can tell
> what went wrong.
>
> 2. It is completely inappropriate to cross-post to four different
> mailing lists.
>
> regards, tom lane
>

"praveen" <praveen.k@renaissance-it.com> writes:
> During configure I find the error in config.log file
> checking for flags to link embedded Perl... Can't locate ExtUtils/Embed.pm
> in @INC (@INC contains: /usr/lib/perl5/5.10.0/i386-linux-thread-multi

Well, there's your problem ...

FYI, our current Fedora RPMs show both ExtUtils::MakeMaker and
ExtUtils::Embed as required to build Postgres from source.

            regards, tom lane

Thanks a lot , Tom Lane.
I installed below mentioned RPMs  and now  it is working
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "praveen" <praveen.k@renaissance-it.com>
Cc: <pgsql-general@postgresql.org>; <pgsql-admin@postgresql.org>
Sent: Wednesday, November 05, 2008 8:54 PM
Subject: Re: [ADMIN] Installation Error of postgresql-8.1.5 with perl.


> "praveen" <praveen.k@renaissance-it.com> writes:
>> During configure I find the error in config.log file
>> checking for flags to link embedded Perl... Can't locate
>> ExtUtils/Embed.pm
>> in @INC (@INC contains: /usr/lib/perl5/5.10.0/i386-linux-thread-multi
>
> Well, there's your problem ...
>
> FYI, our current Fedora RPMs show both ExtUtils::MakeMaker and
> ExtUtils::Embed as required to build Postgres from source.
>
> regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>