Re: [SQL] Extremely Low performance with ODBC
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
On Thu, May 22, 2008 at 11:29 AM, Sebastian Rychter <[EMAIL PROTECTED]> 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. > > Any idea ? 1) turn off all ODBC query logging from both the ODBC driver and the ODBC administrator. This feature is a performance killer. Only use this feature if you are trying to debug a problem. 2) Some client programs try to perform the joins on the client side rather than the server side. I would check if the client is actually passing the actual SQL statement back to the server. If it is trying to perform the join on the client, it could be a big performance killer. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
Thanks.. All logs are already off and I'm just sending one query directly from the application (from Visual Foxpro using SQL Pass through and in VB.net using ado.net). I've found that the "problem" is that I'm using implicit joins, and when I change it to explicit Inner Joins the query only takes 3 seconds (as with PgAdmin). I still can't understand if the problem resides on the ODBC driver, the Query Planner or it's just a mix of both. -Mensaje original- De: Richard Broersma [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 23 de Mayo de 2008 01:04 p.m. Para: Sebastian Rychter CC: pgsql-sql@postgresql.org Asunto: Re: [SQL] Extremely Low performance with ODBC On Thu, May 22, 2008 at 11:29 AM, Sebastian Rychter <[EMAIL PROTECTED]> 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. > > Any idea ? 1) turn off all ODBC query logging from both the ODBC driver and the ODBC administrator. This feature is a performance killer. Only use this feature if you are trying to debug a problem. 2) Some client programs try to perform the joins on the client side rather than the server side. I would check if the client is actually passing the actual SQL statement back to the server. If it is trying to perform the join on the client, it could be a big performance killer. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug __ NOD32 3124 (20080522) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
On Fri, May 23, 2008 at 9:47 AM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: > Thanks.. All logs are already off and I'm just sending one query directly > from the application (from Visual Foxpro using SQL Pass through and in > VB.net using ado.net). I've found that the "problem" is that I'm using > implicit joins, and when I change it to explicit Inner Joins the query only > takes 3 seconds (as with PgAdmin). I still can't understand if the problem > resides on the ODBC driver, the Query Planner or it's just a mix of both. The next step would be to turn on PostgreSQL's statement logging and statement duration. Issue the queries from VFP and then check what statement that PG is actually getting. If the statement that PG is getting looks good, try executing that statement prefixed with explain analyze from the psql command line to help identify where the performance problem is. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
Richard Broersma wrote: On Fri, May 23, 2008 at 9:47 AM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: Thanks.. All logs are already off and I'm just sending one query directly from the application (from Visual Foxpro using SQL Pass through and in VB.net using ado.net). I've found that the "problem" is that I'm using implicit joins, and when I change it to explicit Inner Joins the query only takes 3 seconds (as with PgAdmin). I still can't understand if the problem resides on the ODBC driver, the Query Planner or it's just a mix of both. The next step would be to turn on PostgreSQL's statement logging and statement duration. Issue the queries from VFP and then check what statement that PG is actually getting. If the statement that PG is getting looks good, try executing that statement prefixed with explain analyze from the psql command line to help identify where the performance problem is. You know what'd be incredibly handy for this sort of issue? The ability to set an `always explain analyze' flag on a connection, preferably as a driver parameter (for ODBC/JDBC) or failing that as a per-connection variable like the ones to enable/disable seqscan, nested loop, etc. That might really help with tracking down issues that appear to only happen with queries run by an app, or though a particular interface. Can it be done? Or is the DB server not capable of generating explain output (say to a log) and also returning a resultset? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
On Fri, May 23, 2008 at 2:07 PM, Craig Ringer <[EMAIL PROTECTED]> wrote: > Can it be done? Or is the DB server not capable of generating explain output > (say to a log) and also returning a resultset? It can be done ~kind-of~. To me EXPLAIN [ ANALYSE ] is similar to any other SELECT statement query, with the exception that the data-set that it returns to the client is an order list of execution steps. So, if a client front-end were to pass an explain analyze query to the server, the server would return the data-set for the execution plan. The trick here is to find a way to get a client interface to prefix its select statements with explain analyze. And this, I don't thing can be done. That's why you have to manually view the server logs to see what query are being passed so that identify the problem queries your self. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
ost=0.00..1.02 rows=2 width=4) (actual
time=0.003..0.007 rows=2 loops=1)
-> Index Scan using
"PatientFamily_pkey" on "PatientFamily" (cost=0.00..8.27 rows=1 width=4)
(actual time=0.010..0.012 rows=1 loops=1)
Index Cond:
("PatientFamily"."Family_ID" = "Patient"."Family_ID")
-> Seq Scan on "Title" (cost=0.00..1.10
rows=10 width=4) (actual time=0.004..0.023 rows=10 loops=1)
-> Seq Scan on "Country" "PatientEmployerCountry"
(cost=0.00..1.02 rows=2 width=4) (actual time=0.003..0.007 rows=2 loops=1)
-> Seq Scan on "Country" "PatientAltCountry"
(cost=0.00..1.02 rows=2 width=4) (actual time=0.003..0.006 rows=2 loops=1)
-> Seq Scan on "Country" "PatientCountry" (cost=0.00..1.02
rows=2 width=4) (actual time=0.003..0.006 rows=2 loops=1)
-> Seq Scan on "PatientFamilyRole" (cost=0.00..1.12 rows=12
width=4) (actual time=0.004..0.026 rows=12 loops=1)
Total runtime: 6.446 ms
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de Richard Broersma
Enviado el: Viernes, 23 de Mayo de 2008 05:26 p.m.
Para: Sebastian Rychter
CC: pgsql-sql@postgresql.org
Asunto: Re: [SQL] Extremely Low performance with ODBC
On Fri, May 23, 2008 at 9:47 AM, Sebastian Rychter
<[EMAIL PROTECTED]> wrote:
> Thanks.. All logs are already off and I'm just sending one query directly
> from the application (from Visual Foxpro using SQL Pass through and in
> VB.net using ado.net). I've found that the "problem" is that I'm using
> implicit joins, and when I change it to explicit Inner Joins the query
only
> takes 3 seconds (as with PgAdmin). I still can't understand if the problem
> resides on the ODBC driver, the Query Planner or it's just a mix of both.
The next step would be to turn on PostgreSQL's statement logging and
statement duration. Issue the queries from VFP and then check what
statement that PG is actually getting. If the statement that PG is
getting looks good, try executing that statement prefixed with explain
analyze from the psql command line to help identify where the
performance problem is.
--
Regards,
Richard Broersma Jr.
Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
__ NOD32 3127 (20080523) Information __
This message was checked by NOD32 antivirus system.
http://www.eset.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Extremely Low performance with ODBC
On Fri, May 23, 2008 at 2:45 PM, Sebastian Rychter <[EMAIL PROTECTED]> wrote: > Thanks for your response. I did that in the first place and found that both > explain reports where quite similar. I've got both reports: From pgadmin it > took 7288 ms, and from VFP took 6446 ms (of course, that's the time > calculated within the explain analyze, and not the real elapsed time). actually it looks like 7.288ms and 6.446ms. ;) > That's why I assume the ODBC driver is taking its own time for parsing or > something like that- around 2 minutes actually :) Checking the Postgresql logs would be really helpful here. 1) note the time that you issue the query from your client FE. 2) note the time in the pg server logs that the server gets the passed query string. 2b) check for any disparity between 1) and 2). 3) check that the query is exactly the same as you've hand coded in pg-admin 4) also check the query duration when issued by ODBC in the postgresql log to see if it is really taking 2 minutes. It would be nice to see the snippet of the PG log showing this ODBC query with associate duration times. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
