Re: [PERFORM] slow query using sub select
that worked like a champ nice call as always! thanks Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, May 22, 2006 7:07 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] slow query using sub select Tim Jones [EMAIL PROTECTED] writes: I am having a problem with a sub select query being kinda slow. The query is as follows: select batterycode, batterydescription, observationdate from Battery t1 where patientidentifier=611802158 and observationdate = (select max(observationdate) from Battery t2 where t2.batterycode=t1.batterycode and patientidentifier=611802158) order by batterydescription. Yeah, this is essentially impossible for the planner to optimize, because it doesn't see any way to de-correlate the subselect, so it does it over again for every row. You might find it works better if you cast the thing as a SELECT DISTINCT ON problem (look at the weather report example in the SELECT reference page). regards, tom lane ---(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
[PERFORM] Selects query stats?
All, I might be completely crazy here, but it seems every other database exposes select query stats. Postgres only exposes updates/deletes/ inserts. Is there something I am missing here? Best Regards, Dan Gorman ---(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
Re: [PERFORM] Selects query stats?
Dan Gorman wrote: All, I might be completely crazy here, but it seems every other database exposes select query stats. Postgres only exposes updates/deletes/inserts. Is there something I am missing here? Perhaps. You can EXPLAIN ANALYZE a SELECT, just like i/u/d -- but then you don't get the normal result set back. Is that what you mean? You can turn on log_min_duration_statement and get total SELECT duration logged. There's a thread in pgsql-hackers (Re: Porting MSSQL to PGSQL: trace and profile) about server-side logging of query plans and stats (for all four of s/i/u/d), which is indeed not there in PG. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Selects query stats?
What I am looking for is that our DB is doing X selects a min. Turning on logging isn't an option as it will create too much IO in our enviornment. Regards, Dan Gorman On May 23, 2006, at 11:15 AM, Mischa Sandberg wrote: Dan Gorman wrote: All, I might be completely crazy here, but it seems every other database exposes select query stats. Postgres only exposes updates/ deletes/inserts. Is there something I am missing here? Perhaps. You can EXPLAIN ANALYZE a SELECT, just like i/u/d -- but then you don't get the normal result set back. Is that what you mean? You can turn on log_min_duration_statement and get total SELECT duration logged. There's a thread in pgsql-hackers (Re: Porting MSSQL to PGSQL: trace and profile) about server-side logging of query plans and stats (for all four of s/i/u/d), which is indeed not there in PG. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Selects query stats?
Dan Gorman wrote: What I am looking for is that our DB is doing X selects a min. What specifically would you like to measure? Duration for specific queries? Queries in an app for which you have no source? There may be a way to get what you want by other means ... Details? I gather you cannot just time the app that's doing the selects, nor extract those selects and run them via psql and time them on their own? Dan Gorman wrote: All, I might be completely crazy here, but it seems every other database exposes select query stats. Postgres only exposes updates/deletes/inserts. Is there something I am missing here? ---(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
Re: [PERFORM] Selects query stats?
In any other DB (oracle, mysql) I know how many queries (selects) per second the database is executing. How do I get this number out of postgres?I have a perl script that can test this, but no way the db tells me how fast it's going.(e.g. in oracle: select sum(executions) from v$sqlarea;)Regards,Dan GormanOn May 23, 2006, at 11:32 AM, Mischa Sandberg wrote:Dan Gorman wrote: What I am looking for is that our DB is doing X selects a min. What specifically would you like to measure?Duration for specific queries?Queries in an app for which you have no source?There may be a way to get what you want by other means ...Details?I gather you cannot just time the app that's doing the selects,nor extract those selects and run them via psql and time themon their own? Dan Gorman wrote: All,I might be completely crazy here, but it seems every other database exposes select query stats. Postgres only exposes updates/deletes/inserts. Is there something I am missing here? ---(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
Re: [PERFORM] Selects query stats?
On Tue, May 23, 2006 at 11:33:12AM -0700, Dan Gorman wrote: In any other DB (oracle, mysql) I know how many queries (selects) per second the database is executing. How do I get this number out of postgres? You can't. You also can't know how many DML statements were executed (though you can see how many tuples were inserted/updated/deleted), or how many transactions have occured (well, you can hack the last one, but it's a bit of a mess). It would be nice if all of this was available. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Selects query stats?
On Tue, 2006-05-23 at 11:33 -0700, Dan Gorman wrote: In any other DB (oracle, mysql) I know how many queries (selects) per second the database is executing. How do I get this number out of postgres? I have a perl script that can test this, but no way the db tells me how fast it's going. (e.g. in oracle: select sum(executions) from v$sqlarea;) The Oracle query you show doesn't do that either. It tells you how many statements have been executed since startup, not per second. The main problem with what you ask is it only seems to have value. If the value dips for some reason, you have no way of knowing whether that occurred because the arrival rate dropped off, there is a system problem or whether statements just happened to access more data over that time period. You can collect information that would allow you to understand what is happening on your system and summarise that as you choose. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Selects query stats?
Yeah, I'm not really concerned about the app or sys performance, just a basic question of how do I get the rate of selects that are being executed. In a previous post from Jim, he noted it cannot be done. I am very surprised postgres can't do this basic functionality. Does anyone know if the postgres team is working on this? (btw, I pasted in the wrong oracle query lol - but it can be done in mysql and oracle) Best Regards, Dan Gorman On May 23, 2006, at 11:51 AM, Simon Riggs wrote: On Tue, 2006-05-23 at 11:33 -0700, Dan Gorman wrote: In any other DB (oracle, mysql) I know how many queries (selects) per second the database is executing. How do I get this number out of postgres? I have a perl script that can test this, but no way the db tells me how fast it's going. (e.g. in oracle: select sum(executions) from v$sqlarea;) The Oracle query you show doesn't do that either. It tells you how many statements have been executed since startup, not per second. The main problem with what you ask is it only seems to have value. If the value dips for some reason, you have no way of knowing whether that occurred because the arrival rate dropped off, there is a system problem or whether statements just happened to access more data over that time period. You can collect information that would allow you to understand what is happening on your system and summarise that as you choose. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Selects query stats?
Jim C. Nasby [EMAIL PROTECTED] writes: On Tue, May 23, 2006 at 11:33:12AM -0700, Dan Gorman wrote: In any other DB (oracle, mysql) I know how many queries (selects) per second the database is executing. How do I get this number out of postgres? You can't. You also can't know how many DML statements were executed (though you can see how many tuples were inserted/updated/deleted), or how many transactions have occured (well, you can hack the last one, but it's a bit of a mess). Hack? We do count commits and rollbacks (see pg_stat_database); doesn't seem that hacky to me. Counting individual statements would add overhead (which the OP already declared unacceptable) and there are some definitional issues too, like whether to count statements executed within functions. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Selects query stats?
Tom Lane wrote: Counting individual statements would add overhead (which the OP already declared unacceptable) and there are some definitional issues too, like whether to count statements executed within functions. Yeah, the problem seems underspecified. How do you count statements added or removed by rewrite rules? Statements executed to answer RI queries? Do you count the statements issued by clients as part of the startup sequence? The hypothetical reset session of a connection pool handler? How do you count 2PC -- when they are executed, or when they are committed? What happens to statements in transactions that are rolled back? What happens to a statement that is executed partially because it failed partway (e.g. because of division by zero)? OTOH ISTM it would be easy to modify Postgres so as to count statements in the stat collector, by turning pgstat_report_activity into a routine that sent a count (presumably always 1) instead of the query string, and then just add the count to a counter on receiving. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Selects query stats?
Alvaro Herrera [EMAIL PROTECTED] writes: OTOH ISTM it would be easy to modify Postgres so as to count statements in the stat collector, by turning pgstat_report_activity into a routine that sent a count (presumably always 1) instead of the query string, and then just add the count to a counter on receiving. You wouldn't have to change the backends at all, just modify the collector to count the number of report_activity messages received. Might have to play some games with ignoring IDLE messages, but otherwise simple (and simplistic...) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Selects query stats?
On 5/23/06, Dan Gorman [EMAIL PROTECTED] wrote: What I am looking for is that our DB is doing X selects a min. If you're using 7.4, you can use log_duration to only log duration. It won't log all the query text, only one short line per query. Then you can use pgFouine to analyze this and having a graph such like that http://pgfouine.projects.postgresql.org/reports/sample_hourly.html . If you only log duration, you won't be able to separate insert/delete/update from select though. So it can be interesting only if they negligible. Note that this is not possible in 8.x. You'll have to log the statement to log the duration. I proposed a patch but it was refused as it complexified the log configuration. Turning on logging isn't an option as it will create too much IO in our enviornment. What we do here is logging on another machine via the network using syslog. From our experience, it's not the fact to log that really slows down the db but the generated I/O load. So if you do that, you should be able to log the statements without slowing down your database too much. On our production databases, we keep the log running all the time and we generate reports daily. Regards, -- Guillaume ---(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
Re: [PERFORM] Selects query stats?
On Tue, 2006-05-23 at 15:55 -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: OTOH ISTM it would be easy to modify Postgres so as to count statements in the stat collector, by turning pgstat_report_activity into a routine that sent a count (presumably always 1) instead of the query string, and then just add the count to a counter on receiving. You wouldn't have to change the backends at all, just modify the collector to count the number of report_activity messages received. Might have to play some games with ignoring IDLE messages, but otherwise simple (and simplistic...) The OP wanted statements/sec rather than just a total. Having stats logged by time would be very useful, but I wouldn't limit that just to numbers of statements in each time period. stats_logging_interval = 60 by default, 0 to disable, range 5-3600 -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Selects query stats?
Alvaro Herrera wrote: Yeah, the problem seems underspecified. So, Dan, the question is, what are you trying to measure? This might be a statistic that management has always been given, for Oracle, and you need to produce the same number for PostgreSQL. If not, it's hard to figure out what a statement counter actually can measure, to the extent that you can say, If that number does THIS, I should do THAT. -- Engineers think that equations approximate reality. Physicists think that reality approximates the equations. Mathematicians never make the connection. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
