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.
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
