Dear Alistair, At Tue, 7 Apr 2009 11:23:24 +0100, Alistair Bayley wrote:

2009/4/6 Marko Schütz :

...

I have an application where some simple data extracted from some source files is inserted into a PostgreSQL database. The application uses Takusen and is compiled with GHC 6.8.3. Some (59 in the test data) of the selects take on average 460ms each for a total time for this sample run of 30s. I prepare one select statement at the beginning of the run into which I then bind the specific values for every one of the selects. It does not seem to make a difference whether I do this or whether I just use a new statement for every select.

Hello Marko,

I'm finding it hard to see what the problem is here. Is it that your query takes 460ms, and you need it to be quicker? Or is it something

Yes, the problem was (see below) that I will end up running this program thousands of times and 30s per run is way too long.

else? It would help to have some example code. Can you make a test case which reproduces te problem, that you could share?

...

For comparison, I have collected the SQL statements in a file with PREPARE ... and DEALLOCATE for _every_ select and then run this file through psql. This takes 2s!

If all you are doing is preparing and deallocating - i.e. not executing - then that will be very quick, because the queries are never executed.

I should have mentioned that I do indeed have "an EXECUTE ... in between PREPARE ... and DEALLOCATE". In the meantime I found out what the issue was: I was using `Int` on the Haskell side which was translated to `NUMERIC`, but the relevant columns of the database have type `INT`. Consequently, the indices were not being used! To debug this I used an explicit EXPLAIN ANALYZE ... on the name of the prepared statement from within a withPreparedStatement. Best regards, Marko