Re: [PERFORM] Replacing Cursors with Temporary Tables

2010-04-21 Thread Scott Carey
On Apr 21, 2010, at 1:16 PM, Eliot Gable wrote: > I have previously discussed my very long PL/PGSQL stored procedure on this > list. However, without getting into too many details, I have another > performance-related question. > > The procedure currently uses cursors to return multiple result

Re: [PERFORM] Very high effective_cache_size == worse performance?

2010-04-21 Thread Scott Carey
On Apr 20, 2010, at 12:22 PM, Scott Marlowe wrote: > On Tue, Apr 20, 2010 at 12:47 PM, David Kerr wrote: >> On Tue, Apr 20, 2010 at 02:15:19PM -0400, Robert Haas wrote: >> - On Tue, Apr 20, 2010 at 2:03 PM, David Kerr wrote: >> - > that thought occured to me while I was testing this. I ran a va

Re: [PERFORM] Replacing Cursors with Temporary Tables

2010-04-21 Thread Robert Haas
I think it's really tough to say how this is going to perform. I'd recommend constructing a couple of simplified test cases and benchmarking the heck out of it. One of the problems with temporary tables is that every time you create a temporary table, it creates a (temporary) record in pg_class;

[PERFORM] Re: [PERFORM] Dbt2 with postgres issues on CentOS-5. 3‏

2010-04-21 Thread Mark Wong
2010/4/20 MUHAMMAD ASIF : > Hi, > > I am using dbt2 on Linux 64 (CentOS release 5.3 (Final)) . I have compiled > latest postgresql-8.4.3 code on the machine and run dbt2 against it. I am > little confused about the results. I ran dbt2 with the following > configuration i.e. > > DBT2 Options : >    

[PERFORM] Replacing Cursors with Temporary Tables

2010-04-21 Thread Eliot Gable
I have previously discussed my very long PL/PGSQL stored procedure on this list. However, without getting into too many details, I have another performance-related question. The procedure currently uses cursors to return multiple result sets to the program executing the procedure. Basically, I do

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Kris Jurka
On Wed, 21 Apr 2010, Nikolas Everett wrote: More to the point is there some option that can shift you into method a?  I'm thinking of warehousing type applications where you want to re-plan a good portion of your queries. This can be done by connecting to the database using the V2 protoco

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Kris Jurka
On Wed, 21 Apr 2010, Robert Haas wrote: On Tue, Apr 20, 2010 at 5:05 PM, Kris Jurka wrote: b) Using the parameter values for statistics, but not making any stronger guarantees about them.  So the parameters will be used for evaluating the selectivity, but not to perform other optimizations

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Nikolas Everett
On Wed, Apr 21, 2010 at 11:30 AM, Tom Lane wrote: > >> On Tue, Apr 20, 2010 at 5:05 PM, Kris Jurka wrote: > >>> ... There are three ways to plan a PreparedStatement: > > FWIW, I think there is some consensus to experiment (in the 9.1 cycle) > with making the server automatically try replanning o

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Tom Lane
>> On Tue, Apr 20, 2010 at 5:05 PM, Kris Jurka wrote: >>> ... There are three ways to plan a PreparedStatement: FWIW, I think there is some consensus to experiment (in the 9.1 cycle) with making the server automatically try replanning of parameterized queries with the actual parameter values subs

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Nikolas Everett
On Wed, Apr 21, 2010 at 10:41 AM, Robert Haas wrote: > On Tue, Apr 20, 2010 at 5:05 PM, Kris Jurka wrote: > > The reason this is not done is that the mechanism used for fetching a > piece > > of the results at a time can change the query plan used if using a > > PreparedStatement. There are thr

Re: [JDBC] SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set

2010-04-21 Thread Robert Haas
On Tue, Apr 20, 2010 at 5:05 PM, Kris Jurka wrote: > The reason this is not done is that the mechanism used for fetching a piece > of the results at a time can change the query plan used if using a > PreparedStatement.  There are three ways to plan a PreparedStatement: > > a) Using the exact param

Re: [PERFORM] significant slow down with various LIMIT

2010-04-21 Thread Kevin Grittner
I wrote: > ALTER TABLE ALTER plugins_guide_address > ALTER COLUMN city_id SET STATISTICS 1000; One too many ALTERs in there. Should be: ALTER TABLE plugins_guide_address ALTER COLUMN city_id SET STATISTICS 1000; -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@pos