Is there any method to keep table in memory at startup

On Wed, 21 Apr 2004 vinayj@ncst.ernet.in wrote:

Hi
I am working on a project in postgres..in which i designed customized data type
and operations on it.it requires a look up table..
I have three options regarding this table...
1. Every time a query is executed it creates table assigns values and after
execution destroys it...which is overhead..

2. store table on disk in database and access it whenever required but it
degrades the performance

3. whenever psql starts it can load the table in memory from database which is
efficient way to do

PostgreSQL has no facility to put tables into memory.

Assuming this lookup table will be hit quite often, it WILL be in memory
for selects. updates / deletes / inserts will have to get flushed out to
disk of course.

the Linux and BSD kernels are both quite good at keeping commonly used
data in memory. I think you are mistaken in assuming that an "on disk"
table will be significantly slower than if it was fixed in memory due to
the very efficient cachine of the most common unix kernels.

Hi
thank you for such a useful information...
but actually in my case if i keep table in disk it significantly
degrades performance and even for a table of 10 rows it takes 1-2
minutes I think u r not beliving it ! am i right
for example
I create a table in which i use my customized data type say student
create table student
(Name INDCHAR //INDCHAR is customized data type
age integer);
now i give query like this
select * from student order by name;
it will search for it's comparator operator (<) and related function...
in that function there is one lookup table if that table is in memory no
problem! (oh but it can't be) if it is in disk my program makes
connection to database and execute query which is just a select
statement on a simple where condition of equality. then closes connection
so every time less than operator(<) is called it does the same task..
what i feel in table of 10 rows how many times the < operator will be
called(NO idea but must be > 10 times)
is there any solution..
thanks in advance
regards
vinay
scott.marlowe wrote:

Vinay Jain wrote:

Hi
thank you for such a useful information...
but actually in my case if i keep table in disk it significantly
degrades performance and even for a table of 10 rows it takes 1-2
minutes I think u r not beliving it ! am i right
for example
I create a table in which i use my customized data type say student
create table student
(Name INDCHAR //INDCHAR is customized data type
age integer);
now i give query like this
select * from student order by name;
it will search for it's comparator operator (<) and related function...
in that function there is one lookup table if that table is in memory no
problem! (oh but it can't be) if it is in disk my program makes
connection to database and execute query which is just a select
statement on a simple where condition of equality. then closes connection

There's your problem. Creating database connections is an expensive
operation. They are not intended to be opened and closed often or
quickly. Open your database connection at the beginning of your program,
and close it at the end.

You could also throw an index on the column you're using in your order
by clause, but that won't make a difference until your table get a
little bigger.

Please take further questions of this nature to the pgsql-novice list.

Hi
thanx and sorry that I asked such a simple question in postgres-hackers
list....
but the complexity which i feel on that basis ....please allow me to
explain my problem further.....
As i am working on sorting order , length and substring functions for
Hindi text(Indian Language)...
Here is the problem which i found in postgresql...
after setting collating sequence in proper way(i.e. C) the order was on
basis of unicode values...but in Hindi Language some of combined unicode
values makes a single character
similarly length is not appropriate for these reasons & hence substring
operations
so i designed a customized data type called IndChar....and operations on it
in order by statement the only function called is indchar_lt(defined for
< operator)......
Now please guide me where is starting(where i can open connection to
database) and ending of my program....I feel only in indchar_lt function
which will be called many times in order by statement causing
performance degradation..as i am not much experienced this assumption
may be wrong...
so my question remains as it is that is there any such thing which can
be called at startup of psql.........to make connection to database

regards
Vinay

Andrew Hammond wrote:

Vinay Jain wrote:

Hi
thanx and sorry that I asked such a simple question in postgres-hackers
list....
but the complexity which i feel on that basis ....please allow me to
explain my problem further.....
As i am working on sorting order , length and substring functions for
Hindi text(Indian Language)...
Here is the problem which i found in postgresql...
after setting collating sequence in proper way(i.e. C) the order was on
basis of unicode values...but in Hindi Language some of combined unicode
values makes a single character
similarly length is not appropriate for these reasons & hence substring
operations
so i designed a customized data type called IndChar....and operations on it
in order by statement the only function called is indchar_lt(defined for
< operator)......

Is your indchar_lt function declared IMMUTABLE? That would allow it's
results to be cached instead of re-calculated every time.

Now please guide me where is starting(where i can open connection to
database) and ending of my program....I feel only in indchar_lt function
which will be called many times in order by statement causing
performance degradation..

Have you created an index on that column? That would be a usual way to
speed up an ORDER BY. NB, the function involved must be IMMUTABLE to be
used in an index.

as i am not much experienced this assumption may be wrong...

My professor at University used to always say "measure measure measure".
Postgres makes it easy to measure. Try putting EXPLAIN ANALYZE before
your SELECT statement. Run the SELECT a couple of times first so that
the OS can get stuff cached, then:

do an EXPLAIN ANALYZE on the query, save the results

then ANALYZE the tables involved and to another EXPLAIN ANALYZE on the
query, save the results

add the necessary index, ANALYZE then EXPLAIN ANALYZE.

so my question remains as it is that is there any such thing which can
be called at startup of psql.........to make connection to database

I'm really not sure what you mean by this. psql connects to the database
on startup.

hi
ya function is declared immutable and strict... also made btree index..
one mistake that i was doing that I was using libpq rather than
SPI..but even after using it though no doubt the performance is
increased still not up to the mark
if i use index than it's explain analyze is like this :

// For 11 rows

template1=# explain ANALYZE select * from temp_hindi_copy3 order by name;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using temp_hindi_copy3_pkey on temp_hindi_copy3
(cost=0.00..52.00 rows=1000 width=36) (actual time=0.06..0.12 rows=11
loops=1)
Total runtime: 0.24 msec
which is good and obvious but when i have to use other functions with
it like

// 11 Rows

template1=# explain ANALYZE select name, length(name), substr(name, 3,
2) from temp_hindi_copy3 order by name;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using temp_hindi_copy3_pkey on temp_hindi_copy3
(cost=0.00..52.00 rows=1000 width=32) (actual time=88.72..920.18 rows=11
loops=1)
Total runtime: 920.37 msec
(2 rows)
which is not good
similarly for Non Key order by:

//136 Rows
template1=# explain ANALYZE select * from temp_hindi_copy order by name;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Sort (cost=69.83..72.33 rows=1000 width=36) (actual
time=26282.57..26282.68 rows=136 loops=1)
Sort Key: name
-> Seq Scan on temp_hindi_copy (cost=0.00..20.00 rows=1000
width=36) (actual time=0.22..0.60 rows=136 loops=1)
Total runtime: 26282.92 msec
(4 rows)

Which takes a lot of time the reason is that in every indchar_lt
function call SPI connection is made and destroyed...
and order by clause calls it n (> no. of rows) times..
I tried a more than 10 times still the output was same.......... also in
one order by statement the same query is called more than n times....
but performance is same....
so is there any way to just make connection once and keep data in
memory...and when psql is exited the connection is closed...

When we start psql it makes connection with database is correct...
but not from program ....so have to Make SPI connection...

Andrew Hammond wrote:

Vinay Jain wrote:

Hi
thanx and sorry that I asked such a simple question in
postgres-hackers list....
but the complexity which i feel on that basis ....please allow me to
explain my problem further.....
As i am working on sorting order , length and substring functions for
Hindi text(Indian Language)...
Here is the problem which i found in postgresql...
after setting collating sequence in proper way(i.e. C) the order was
on basis of unicode values...but in Hindi Language some of combined
unicode values makes a single character
similarly length is not appropriate for these reasons & hence
substring operations
so i designed a customized data type called IndChar....and operations
on it
in order by statement the only function called is indchar_lt(defined
for < operator)......

is your indchar_lt function declared IMMUTABLE? That would allow it's
results to be cached instead of re-calculated every time.

Vinay Jain <vinayj@sarathi.ncst.ernet.in> writes:

Which takes a lot of time the reason is that in every indchar_lt
function call SPI connection is made and destroyed...

I cannot imagine how you'd think that that would be practical from a
performance standpoint.

Here is the problem which i found in postgresql...
after setting collating sequence in proper way(i.e. C) the order was
on basis of unicode values...but in Hindi Language some of combined
unicode values makes a single character
similarly length is not appropriate for these reasons & hence
substring operations

Why don't you just use a Hindi locale? The infrastructure should all be
there already for you.

regards, tom lane

Vinay Jain said:

hi
ya function is declared immutable and strict... also made btree
index..

The docs state this about immutable functions:

IMMUTABLE indicates that the function always returns the same result when
given the same argument values; that is, it does not do database lookups
or otherwise use information not directly present in its argument list.

So it appears you are lying to postgres when you declare your function to
be immutable, because you are doing a database lookup.

Why not just code your lookup table as static data immediately available
to your function, and look it up from C directly, rather than using a
database table? Then your function could be genuinely immutable (and fast).

cheers

andrew

Tom Lane wrote:

Vinay Jain <vinayj@sarathi.ncst.ernet.in> writes:

Which takes a lot of time the reason is that in every indchar_lt
function call SPI connection is made and destroyed...

I cannot imagine how you'd think that that would be practical from a
performance standpoint.

Here is the problem which i found in postgresql...
after setting collating sequence in proper way(i.e. C) the order was
on basis of unicode values...but in Hindi Language some of combined
unicode values makes a single character
similarly length is not appropriate for these reasons & hence
substring operations

Why don't you just use a Hindi locale? The infrastructure should all be
there already for you.

regards, tom lane

Hi
Not actually even in Hindi Locale the output was incorrect..i.e. sort
order was wrong
and also length and substring operations
which are not based on syllables.

Andrew Dunstan wrote:

Vinay Jain said:

hi
ya function is declared immutable and strict... also made btree
index..

The docs state this about immutable functions:

IMMUTABLE indicates that the function always returns the same result when
given the same argument values; that is, it does not do database lookups
or otherwise use information not directly present in its argument list.

So it appears you are lying to postgres when you declare your function to
be immutable, because you are doing a database lookup.

Why not just code your lookup table as static data immediately available
to your function, and look it up from C directly, rather than using a
database table? Then your function could be genuinely immutable (and fast).

cheers

andrew

Hi

You mean that I should create a static table in C program itself and use
it...if i am not wrong
Ya for the time being i am doing this thing but actually table is bigger
(around 5000 rows with 6 columns)
also this table is also created and destroyed in each indchar_lt call
which is called many times in order by clause
one more thing i want it generalized so that I can include other indian
languages also..without changing code..
If there is not any method to get results fast using database table i
will have to opt this option only..
regards
Vinay

Vinay Jain wrote:

You mean that I should create a static table in C program itself and
use it...if i am not wrong
Ya for the time being i am doing this thing but actually table is
bigger (around 5000 rows with 6 columns)

bigger than what?

also this table is also created and destroyed in each indchar_lt call
which is called many times in order by clause

uh ... maybe you need to look in your C manual about the effect of a
"static" declaration. The object will be created once.

one more thing i want it generalized so that I can include other
indian languages also..without changing code..
If there is not any method to get results fast using database table i
will have to opt this option only..

Not for what you want - you are pursuing a chimera, IMNSHO.

cheers

andrew

Vinay Jain <vinayj@sarathi.ncst.ernet.in> writes:

Not actually even in Hindi Locale the output was incorrect..i.e. sort
order was wrong
and also length and substring operations
which are not based on syllables.

Hm, possibly you weren't using the same character set encoding that the
locale was expecting? It's not very well documented, but every locale
setting works only with a specific encoding.

If the locale definition really is wrong for your purposes, it seems
like what you want to do is write a new locale definition that does what
you want. Then you could use it with any Unix program, not only
Postgres. (I've never done this, but I can't see that it would be any
harder than writing C code inside Postgres to do it ...)

regards, tom lane

Tom Lane wrote:

Vinay Jain <vinayj@sarathi.ncst.ernet.in> writes:

Not actually even in Hindi Locale the output was incorrect..i.e. sort
order was wrong
and also length and substring operations
which are not based on syllables.

Hm, possibly you weren't using the same character set encoding that the
locale was expecting? It's not very well documented, but every locale
setting works only with a specific encoding.

I kept server side encoding to hi_IN.UTF-8...(Hindi Locale)
also tested changing my OS encoding to the same.....
so i think that I was using right character set encoding..

If the locale definition really is wrong for your purposes, it seems
like what you want to do is write a new locale definition that does what
you want. Then you could use it with any Unix program, not only
Postgres. (I've never done this, but I can't see that it would be any
harder than writing C code inside Postgres to do it ...)

Locale defination is not wrong for my purpose only ordering , length and substring operations are incorrect (in postgres these operations are based on either character or unicodes not on syllables)which i corrected using my own data type and operations

and this is not the case with Postgres only even in MS-SQL the problem
is same...Research work is going on Ordering issues of Hindi and this
project is part of it
I opted PostgreSQL because It gives flexibility to design own data type
and operations in a nice way...
only problem is performance.........because I have to look up a table
while doing comparision and want to keep this table in DataBase

regards, tom lane

regards

Vinay Jain

Andrew Dunstan wrote:

Vinay Jain wrote:

You mean that I should create a static table in C program itself and
use it...if i am not wrong
Ya for the time being i am doing this thing but actually table is
bigger (around 5000 rows with 6 columns)

bigger than what?

also this table is also created and destroyed in each indchar_lt call
which is called many times in order by clause

uh ... maybe you need to look in your C manual about the effect of a
"static" declaration. The object will be created once.

yup I know the effect of Static but this is also fact that when
program terminates and restarts object will be created again...(not
persistant)..

one thing strikes in my mind is that indchar_lt is function in shared
object file indchar.so.....if this file is not unloaded during Order by
call than this static defination can work for me..
Not sure but I think this file is loaded each time indchar_lt is called
in order by clause...

one more thing i want it generalized so that I can include other
indian languages also..without changing code..
If there is not any method to get results fast using database table i
will have to opt this option only..

Not for what you want - you are pursuing a chimera, IMNSHO.

cheers

andrew
regards

Vinay jain

Vinay Jain said:

Andrew Dunstan wrote:

uh ... maybe you need to look in your C manual about the effect of a
"static" declaration. The object will be created once.

yup I know the effect of Static but this is also fact that when
program terminates and restarts object will be created again...(not
persistant)..

you can have it preloaded and persistent to the end of the server run, see
http://www.postgresql.org/docs/current/static/runtime-config.html#RUNTIME-
CONFIG-RESOURCE

one thing strikes in my mind is that indchar_lt is function in shared
object file indchar.so.....if this file is not unloaded during Order by
call than this static defination can work for me..
Not sure but I think this file is loaded each time indchar_lt is called
in order by clause...

If course it is not loaded each time. That would be insane. If not
preloaded it is loaded when first called in each process, and then kept.
It is never unloaded (except by the termination of the process that loaded
it).

It seems you have been laboring under a misapprehension.

cheers

andrew

Andrew Dunstan wrote:

Vinay Jain said:

Andrew Dunstan wrote:

uh ... maybe you need to look in your C manual about the effect of a
"static" declaration. The object will be created once.

yup I know the effect of Static but this is also fact that when
program terminates and restarts object will be created again...(not
persistant)..

you can have it preloaded and persistent to the end of the server run, see
http://www.postgresql.org/docs/current/static/runtime-config.html#RUNTIME-
CONFIG-RESOURCE

one thing strikes in my mind is that indchar_lt is function in shared
object file indchar.so.....if this file is not unloaded during Order by
call than this static defination can work for me..
Not sure but I think this file is loaded each time indchar_lt is called
in order by clause...

If course it is not loaded each time. That would be insane. If not
preloaded it is loaded when first called in each process, and then kept.
It is never unloaded (except by the termination of the process that loaded
it).

It seems you have been laboring under a misapprehension.

If this is the case than I can make connection to data base in starting
of indchar.so file and close connection at end of it and it should work....
bingo!!
it would solve my problem

Vinay Jain said:

Andrew Dunstan wrote:

If course it is not loaded each time. That would be insane. If not
preloaded it is loaded when first called in each process, and then
kept. It is never unloaded (except by the termination of the process
that loaded it).

It seems you have been laboring under a misapprehension.

If this is the case than I can make connection to data base in starting
of indchar.so file and close connection at end of it and it should
work.... bingo!!
it would solve my problem

*sigh*

You are not getting it.

An immutable function MUST NOT DEPEND ON DATA IN THE DATABASE. Sorry to
shout but you really need to understand this.

Use static C data, not database tables. In addition to it being pure, it
will also be enormously faster than getting data from the database. Yes it
means that if you want to change the lookup data you need to recompile
your C function library, and to redo any indexes etc. that depend on the
function. That's just the way it is, I'm afraid.

cheers

andrew

Andrew Dunstan wrote:

Vinay Jain said:

Andrew Dunstan wrote:

If course it is not loaded each time. That would be insane. If not
preloaded it is loaded when first called in each process, and then
kept. It is never unloaded (except by the termination of the process
that loaded it).

It seems you have been laboring under a misapprehension.

If this is the case than I can make connection to data base in starting
of indchar.so file and close connection at end of it and it should
work.... bingo!!
it would solve my problem

*sigh*

You are not getting it.

An immutable function MUST NOT DEPEND ON DATA IN THE DATABASE. Sorry to
shout but you really need to understand this.

Use static C data, not database tables. In addition to it being pure, it
will also be enormously faster than getting data from the database. Yes it
means that if you want to change the lookup data you need to recompile
your C function library, and to redo any indexes etc. that depend on the
function. That's just the way it is, I'm afraid.

cheers

andrew
Hi

ya you are right but i wanted to escape from compilation and stuff....

I will think on declaring function stable rather than immutable and test
performance....
for the time being i will follow your advice...
thanks
regards
Vinay