Re: Extremely Low performance with ODBC

[Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>]

Sebastian Rychter wrote:
> Hi, I'm executing a query through psql ODBC which is taking around 2 minutes
> to complete. When I run it from PgAdmin it takes less than 3 seconds.
>
> The query itself has :
>
> . 15 inner joins (from just around 10 different tables - the other inner
> joins are using different aliases for the same tables) 
>
> . Select statement returns 1 field.
>
> . the testing database is selecting only 1 record.
>
>  
>
> Taking a look at the explain analyze report, I see they are both quite the
> same and tested the ODBC driver through Visual Foxpro and Vb.NET as well,
> taking both around 2 minutes to finish.

Are you using prepared statements (or might the driver be doing so for 
you) ?

A common question here arises from the query planner making different 
decisions for a query based on whether or not it can see the values of 
query parameters. Consider:

SELECT something FROM tablex WHERE somethingelse = ?

vs

SELECT something FROM tablex WHERE somethingelse = 4

My understanding is that if only (say) 0.1% of records have 
`somethingelse' = 4 and there's an index on `somethingelse' the planner 
will probably use the index for the second query. For the first query it 
won't know to use the index, especially if there are also values for 
`somethingelse' that occur a lot.

Try running your query in psql/pgadmin using PREPARE and EXECUTE and see 
if you get the same result.

--
Craig Ringer