Thread: in memory views
is there a possibility for creating views or temp tables in memory to avoid disk io when user makes select operations? regards tom
Thomas Vatter schrieb: > is there a possibility for creating views or temp tables in memory to > avoid disk io when user makes select operations? No need. The data will be available in OS and database caches if they are really required often. If not, tune up the caches and do a regular "pre select". Regards Tino
Tino Wildenhain wrote: > Thomas Vatter schrieb: > >> is there a possibility for creating views or temp tables in memory to >> avoid disk io when user makes select operations? > > > No need. The data will be available in OS and database caches if > they are really required often. If not, tune up the caches and > do a regular "pre select". > > Regards > Tino > > hmm, I am selecting a resultset with 1300 rows joined from 12 tables. with jdbc I am waiting 40 seconds until the first row appears. The following rows appear really fast but the 40 seconds are a problem. regards tom
Thomas Vatter schrieb: > Tino Wildenhain wrote: > >> Thomas Vatter schrieb: >> >>> is there a possibility for creating views or temp tables in memory to >>> avoid disk io when user makes select operations? >> >> >> >> No need. The data will be available in OS and database caches if >> they are really required often. If not, tune up the caches and >> do a regular "pre select". >> >> Regards >> Tino >> >> > > hmm, I am selecting a resultset with 1300 rows joined from 12 tables. > with jdbc I am waiting 40 seconds until the first row appears. The > following rows appear really fast but the 40 seconds are a problem. Well you will need the equally 40 seconds to fill your hypothetical in memory table. (even a bit more due to the creation of a datastructure). So you can do the aproaches of semi materialized views (that are in fact writing into a shadow table) or just prefetch your data at time - just at the times you would refill your memory tables if they existed. A cronjob with select/fetch should do. Regards Tino
Tino Wildenhain wrote: > Thomas Vatter schrieb: > >> Tino Wildenhain wrote: >> >>> Thomas Vatter schrieb: >>> >>>> is there a possibility for creating views or temp tables in memory >>>> to avoid disk io when user makes select operations? >>> >>> >>> >>> >>> No need. The data will be available in OS and database caches if >>> they are really required often. If not, tune up the caches and >>> do a regular "pre select". >>> >>> Regards >>> Tino >>> >>> >> >> hmm, I am selecting a resultset with 1300 rows joined from 12 tables. >> with jdbc I am waiting 40 seconds until the first row appears. The >> following rows appear really fast but the 40 seconds are a problem. > > > Well you will need the equally 40 seconds to fill your hypothetical > in memory table. (even a bit more due to the creation of a > datastructure). > > So you can do the aproaches of semi materialized views (that are in fact > writing into a shadow table) or just prefetch your data at time - just > at the times you would refill your memory tables if they existed. > A cronjob with select/fetch should do. > > Regards > Tino > > If the in memory table is created a bootup time of the dbms it is already present when user selects the data. Of course the challenge is to keep the in memory table up to date if data are changed. What do you mean with semi materialized views, I have tried select * from this_view with the same result. Also, if I repeat the query it does not run faster. regards tom -- Mit freundlichen Grüßen / Regards Vatter Network Inventory Software Sun Microsystems Principal Partner www.network-inventory.de Tel. 030-79782510 E-Mail thomas.vatter@network-inventory.de
Hi, there was a similar discussion with a ramdisk: http://archives.postgresql.org/pgsql-hackers/2005-11/msg01058.php You need to populate the data on serverstart, of course. But as Timo mentionend, it's maybe not worth the trouble. Maybe their is a way to speed up the queriy itself. To analyze this, you should post the query- and table-definition and the output of explain analyze of the offending query. Best regards Hakan Kocaman Software-Development digame.de GmbH Richard-Byrd-Str. 4-8 50829 Köln Tel.: +49 (0) 221 59 68 88 31 Fax: +49 (0) 221 59 68 88 98 Email: hakan.kocaman@digame.de > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of > Thomas Vatter > Sent: Wednesday, May 10, 2006 12:43 PM > To: Tino Wildenhain > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] in memory views > > > Tino Wildenhain wrote: > > > Thomas Vatter schrieb: > > > >> Tino Wildenhain wrote: > >> > >>> Thomas Vatter schrieb: > >>> > >>>> is there a possibility for creating views or temp tables > in memory > >>>> to avoid disk io when user makes select operations? > >>> > >>> > >>> > >>> > >>> No need. The data will be available in OS and database caches if > >>> they are really required often. If not, tune up the caches and > >>> do a regular "pre select". > >>> > >>> Regards > >>> Tino > >>> > >>> > >> > >> hmm, I am selecting a resultset with 1300 rows joined from > 12 tables. > >> with jdbc I am waiting 40 seconds until the first row appears. The > >> following rows appear really fast but the 40 seconds are a problem. > > > > > > Well you will need the equally 40 seconds to fill your hypothetical > > in memory table. (even a bit more due to the creation of a > > datastructure). > > > > So you can do the aproaches of semi materialized views > (that are in fact > > writing into a shadow table) or just prefetch your data at > time - just > > at the times you would refill your memory tables if they existed. > > A cronjob with select/fetch should do. > > > > Regards > > Tino > > > > > > If the in memory table is created a bootup time of the dbms it is > already present when user selects the data. Of course the > challenge is > to keep the in memory table up to date if data are changed. > What do you > mean with semi materialized views, I have tried select * from > this_view > with the same result. Also, if I repeat the query it does not > run faster. > > regards > tom > > -- > Mit freundlichen Grüßen / Regards > Vatter > > Network Inventory Software > Sun Microsystems Principal Partner > > www.network-inventory.de > Tel. 030-79782510 > E-Mail thomas.vatter@network-inventory.de > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Thomas Vatter schrieb: > Tino Wildenhain wrote: ... >> Well you will need the equally 40 seconds to fill your hypothetical >> in memory table. (even a bit more due to the creation of a >> datastructure). >> >> So you can do the aproaches of semi materialized views (that are in fact >> writing into a shadow table) or just prefetch your data at time - just >> at the times you would refill your memory tables if they existed. >> A cronjob with select/fetch should do. >> >> Regards >> Tino >> >> > > If the in memory table is created a bootup time of the dbms it is > already present when user selects the data. Of course the challenge is > to keep the in memory table up to date if data are changed. What do you > mean with semi materialized views, I have tried select * from this_view > with the same result. Also, if I repeat the query it does not run faster. > Semi materialized views are just views with aditional rules and some triggers which copy data to another table. There are several receipes if you google accordingly. I do not know what you mean by "bootup time" - do you really reboot your database server? *hehe* just kidding ;) In your first email you told me your query indeed runs faster the 2nd time (due to the caching) now you are telling me that it is not. Btw, judging from your analyze output you are using very cryptic table and column names - you can use aliasing in the query and dont have to resort to tiny tags when you actually name the objects ;) Maybe others have comments on your query. Btw, better use explain analyze to get realistic results. Regards Tino
Tino Wildenhain wrote: > Thomas Vatter schrieb: > >> Tino Wildenhain wrote: > > ... > >>> Well you will need the equally 40 seconds to fill your hypothetical >>> in memory table. (even a bit more due to the creation of a >>> datastructure). >>> >>> So you can do the aproaches of semi materialized views (that are in >>> fact >>> writing into a shadow table) or just prefetch your data at time - just >>> at the times you would refill your memory tables if they existed. >>> A cronjob with select/fetch should do. >>> >>> Regards >>> Tino >>> >>> >> >> If the in memory table is created a bootup time of the dbms it is >> already present when user selects the data. Of course the challenge >> is to keep the in memory table up to date if data are changed. What >> do you mean with semi materialized views, I have tried select * from >> this_view with the same result. Also, if I repeat the query it does >> not run faster. >> > Semi materialized views are just views with aditional rules and some > triggers which copy data to another table. There are several receipes > if you google accordingly. > > I do not know what you mean by "bootup time" - do you really reboot > your database server? *hehe* just kidding ;) > > In your first email you told me your query indeed runs faster the 2nd > time (due to the caching) now you are telling me that it is not. > > Btw, judging from your analyze output you are using very cryptic > table and column names - you can use aliasing in the query and dont > have to resort to tiny tags when you actually name the objects ;) > > Maybe others have comments on your query. Btw, better use > explain analyze to get realistic results. > > Regards > Tino > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > The subsequent rows are shown faster not the subsequent queries - if you really read my first e-mail ;-) . Yes, I have done analyse yesterday, the database has not changed, afaik it is necessary when the database contents are changing. regards tom
On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote: > Tino Wildenhain wrote: > > > Thomas Vatter schrieb: > > > >> is there a possibility for creating views or temp tables in memory to > >> avoid disk io when user makes select operations? > > > > > > No need. The data will be available in OS and database caches if > > they are really required often. If not, tune up the caches and > > do a regular "pre select". > > > > Regards > > Tino > > > > > > hmm, I am selecting a resultset with 1300 rows joined from 12 tables. > with jdbc I am waiting 40 seconds until the first row appears. The > following rows appear really fast but the 40 seconds are a problem. Are you selecting the whole set at once? Or are you placing it into a cursor? What happens if you do this by declaring it as a cursor and then fetching the first row?
Scott Marlowe wrote:
I do executeQuery(), for the resultSet I do next() and return one row, but wait, I have to review the logic in this area, I can tell you tomorrow
regards
tom
On Wed, 2006-05-10 at 04:55, Thomas Vatter wrote:Tino Wildenhain wrote:Thomas Vatter schrieb:is there a possibility for creating views or temp tables in memory to avoid disk io when user makes select operations?No need. The data will be available in OS and database caches if they are really required often. If not, tune up the caches and do a regular "pre select". Regards Tinohmm, I am selecting a resultset with 1300 rows joined from 12 tables. with jdbc I am waiting 40 seconds until the first row appears. The following rows appear really fast but the 40 seconds are a problem.Are you selecting the whole set at once? Or are you placing it into a cursor? What happens if you do this by declaring it as a cursor and then fetching the first row? ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
I do executeQuery(), for the resultSet I do next() and return one row, but wait, I have to review the logic in this area, I can tell you tomorrow
regards
tom
On Wed, 2006-05-10 at 10:41, Thomas Vatter wrote: > Scott Marlowe wrote: > > What happens if you do this by declaring it as a cursor and then > > fetching the first row? > > > > I do executeQuery(), for the resultSet I do next() and return one row, > but wait, I have to review the logic in this area, I can tell you > tomorrow A good short test is to run explain analyze on the query from the psql command line. If it shows an execution time of significantly less than what you get from you application, then it is likely that the real problem is that your application is receiving the whole result set via libpq and waiting for that. A cursor will solve that problem.
> is there a possibility for creating views or temp tables in memory to > avoid disk io when user makes select operations? you might also want to look into "materialized views": http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html http://www.varlena.com/varlena/GeneralBits/64.php this helped us alot when we had slow queries involving many tables. cheers, thomas
Scott Marlowe wrote:
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType, resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
Yes, the difference between psql command line and application is 6 seconds to 40 seconds. It isOn Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:Scott Marlowe wrote:What happens if you do this by declaring it as a cursor and then fetching the first row?I do executeQuery(), for the resultSet I do next() and return one row, but wait, I have to review the logic in this area, I can tell you tomorrowA good short test is to run explain analyze on the query from the psql command line. If it shows an execution time of significantly less than what you get from you application, then it is likely that the real problem is that your application is receiving the whole result set via libpq and waiting for that. A cursor will solve that problem. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType, resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote: > > > Yes, the difference between psql command line and application is 6 > seconds to 40 seconds. It is > exactly the step resultSet = excecuteQuery() that needs 40 seconds. I > use next() as a cursor > through the resultSet, but I fear this is not enough, do I have to use > createStatement(resultSetType, > resultSetConcurrency) respectively prepareStatement (resultSetType, > resultSetConcurrency) to > achieve the cursor behaviour? Not sure. I don't use a lot of prepared statements. I tend to build queries and throw the at the database. In that instance, it's done like: create cursor cursorname as select (rest of query here); fetch from cursorname; You can find more on cursors here: http://www.postgresql.org/docs/8.1/interactive/sql-declare.html Not sure if you can use them with prepared statements, or if prepared statements have their own kind of implementation.
Are you using the Postgres JDBC driver? Or are you using an ODBC JDBC driver? The Postgres specific driver is usually faster.
-----Original Message-----Scott Marlowe wrote:
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Thomas Vatter
Sent: Wednesday, May 10, 2006 3:54 PM
To: Scott Marlowe
Cc: Tino Wildenhain; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] in memory viewsYes, the difference between psql command line and application is 6 seconds to 40 seconds. It isOn Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:Scott Marlowe wrote:What happens if you do this by declaring it as a cursor and then fetching the first row?I do executeQuery(), for the resultSet I do next() and return one row, but wait, I have to review the logic in this area, I can tell you tomorrowA good short test is to run explain analyze on the query from the psql command line. If it shows an execution time of significantly less than what you get from you application, then it is likely that the real problem is that your application is receiving the whole result set via libpq and waiting for that. A cursor will solve that problem. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType, resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
Dave Dutcher wrote:
I'm using the postgres driver
regards
tom
Are you using the Postgres JDBC driver? Or are you using an ODBC JDBC driver? The Postgres specific driver is usually faster.
I'm using the postgres driver
regards
tom
-----Original Message-----Scott Marlowe wrote:
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Thomas Vatter
Sent: Wednesday, May 10, 2006 3:54 PM
To: Scott Marlowe
Cc: Tino Wildenhain; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] in memory viewsYes, the difference between psql command line and application is 6 seconds to 40 seconds. It isOn Wed, 2006-05-10 at 10:41, Thomas Vatter wrote:Scott Marlowe wrote:What happens if you do this by declaring it as a cursor and then fetching the first row?I do executeQuery(), for the resultSet I do next() and return one row, but wait, I have to review the logic in this area, I can tell you tomorrowA good short test is to run explain analyze on the query from the psql command line. If it shows an execution time of significantly less than what you get from you application, then it is likely that the real problem is that your application is receiving the whole result set via libpq and waiting for that. A cursor will solve that problem. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
exactly the step resultSet = excecuteQuery() that needs 40 seconds. I use next() as a cursor
through the resultSet, but I fear this is not enough, do I have to use createStatement(resultSetType,
resultSetConcurrency) respectively prepareStatement (resultSetType, resultSetConcurrency) to
achieve the cursor behaviour?
regards
tom
-- Mit freundlichen Grüßen / Regards Vatter Network Inventory Software Sun Microsystems Principal Partner www.network-inventory.de Tel. 030-79782510 E-Mail thomas.vatter@network-inventory.de
Scott Marlowe wrote:
Yes, I have used embedded sql and create cursor, fetch before I started with jdbc, seems that
I have to find out if new jdbc has a better way than simply resultSet = statement.executeQuery().
regards
tom
On Wed, 2006-05-10 at 15:54, Thomas Vatter wrote:Yes, the difference between psql command line and application is 6 seconds to 40 seconds. It is exactly the step resultSet = excecuteQuery() that needs 40 seconds. I use next() as a cursor through the resultSet, but I fear this is not enough, do I have to use createStatement(resultSetType, resultSetConcurrency) respectively prepareStatement (resultSetType, resultSetConcurrency) to achieve the cursor behaviour?Not sure. I don't use a lot of prepared statements. I tend to build queries and throw the at the database. In that instance, it's done like: create cursor cursorname as select (rest of query here); fetch from cursorname; You can find more on cursors here: http://www.postgresql.org/docs/8.1/interactive/sql-declare.html Not sure if you can use them with prepared statements, or if prepared statements have their own kind of implementation. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Yes, I have used embedded sql and create cursor, fetch before I started with jdbc, seems that
I have to find out if new jdbc has a better way than simply resultSet = statement.executeQuery().
regards
tom
On Wed, 10 May 2006, Thomas Vatter wrote: > Yes, the difference between psql command line and application is 6 > seconds to 40 seconds. It is exactly the step resultSet = > excecuteQuery() that needs 40 seconds. I use next() as a cursor through > the resultSet, but I fear this is not enough, do I have to use > createStatement(resultSetType, resultSetConcurrency) respectively > prepareStatement (resultSetType, resultSetConcurrency) to achieve the > cursor behaviour? http://jdbc.postgresql.org/documentation/81/query.html#query-with-cursor Kris Jurka
Kris Jurka wrote: > > > On Wed, 10 May 2006, Thomas Vatter wrote: > >> Yes, the difference between psql command line and application is 6 >> seconds to 40 seconds. It is exactly the step resultSet = >> excecuteQuery() that needs 40 seconds. I use next() as a cursor >> through the resultSet, but I fear this is not enough, do I have to >> use createStatement(resultSetType, resultSetConcurrency) respectively >> prepareStatement (resultSetType, resultSetConcurrency) to achieve the >> cursor behaviour? > > > http://jdbc.postgresql.org/documentation/81/query.html#query-with-cursor > > Kris Jurka I was just returning to my mailbox to report success, I was just a bit faster than your e-mail, I have found the fetchSize function, it reduces the delay to 6 seconds. thanks a lot to all who helped, this was really great support, I am glad that the problem is solved tom