many tables vs large tables

Started by Kevin Neufeldover 17 years ago7 messagesgeneral

kneufeld@refractions.net

over 17 years ago

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

praveen

praveen.k@renaissance-it.com

over 17 years ago

In reply to: Kevin Neufeld (#1)

Installation Error of postgresql-8.1.5 with perl.

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.

praveen

praveen.k@renaissance-it.com

over 17 years ago

In reply to: Kevin Neufeld (#1)

Re: Installation Error of postgresql-8.1.5 with perl.

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.

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

Thanks & regard
Praveen kumar.

Tom Lane

tgl@sss.pgh.pa.us

over 17 years ago

In reply to: praveen (#3)

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

over 17 years ago

In reply to: Kevin Neufeld (#1)

Re: Installation Error of postgresql-8.1.5 with perl.

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.

Show quoted text

"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

Tom Lane

tgl@sss.pgh.pa.us

over 17 years ago

In reply to: praveen (#5)

Re: 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

praveen

praveen.k@renaissance-it.com

over 17 years ago

In reply to: Kevin Neufeld (#1)

Re: Installation Error of postgresql-8.1.5 with perl.

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.

Show quoted text

"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