Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(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
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Is there any method to keep table in memory at startup
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 cheers andrew thanx Vinay Jain ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is there any method to keep table in memory at startup
Tom Lane wrote: Vinay Jain [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at 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 IndCharand 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 programI 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. regards Vinay Andrew Hammond 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
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at startup
Tom Lane wrote: Vinay Jain [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Is there any method to keep table in memory at startup
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 ---(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
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Is there any method to keep table in memory at startup
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 IndCharand 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 programI 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. regards Vinay Andrew Hammond 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. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
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 IndCharand 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 programI 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 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. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is there any method to keep table in memory at startup
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. 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is there any method to keep table in memory at startup
On Wed, 21 Apr 2004 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at startup
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: On Wed, 21 Apr 2004 [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
