[SQL] how to use explain analyze
I'm new to postgres and was wondering how to use EXPLAIN ANALYZE
Can I use the output from ANALYZE EXPLAIN to estimate or predict the
actual time
it would take for a given query to return?
I ask because I'm writing a typical web app that allows the user to
build and submit a query
to my DB. Since I don't know how "simple" or "complex" the user-
generated queries will be
I thought it might be possible to use the ANALYZE EXPLAIN output to
make a "guestimation"
about the expected return time of the query.
I'd like to use this in my web-app to determine whether to run the
query in real-time (user waits
for results) or queue up the query (and notify the user once the query
is finished). E.g.:
if (the Total runtime" reported by explain analyze is > n ms) {
tell the user that his request was submitted for processing, and
notify the user once resuilts are available
} else {
run the query and wait for the results in real time.
}
Thanks,
Alan
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: on update restrict
On Tue, 14 Aug 2001, Jan Wieck wrote: > Mister ics wrote: > > testdb=>create table t1 ( > >id int primary key, > >foo int); > > testdb=>create table t2 ( > >id int primary key, > >ref int references t1(id) on update restrict); > So an attempt to > > UPDATE t1 SET id = 2 WHERE id = 1; > > is the thing prevented in your above example. I find it odd that you specify a restiction on one table in the definition of another table. Sorry, if this was a double post. Alan Gutierrez ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Re: DateDiff, IsNull?
Tom, Manuel, Ross Not only do I learn that PostgreSQL supports operator overloading, I learn that this is not a bad thing. It's a big day for me! I tend to think of operator overloading as an over used, non-portable C++ feature. I could be wrong about C++ too. Probably am. Don't want to debate C++ here! Its just that C++ is what made my knee jerk. Thanks for showing me the value of operator overloading in PostgreSQL. Alan On Tue, 14 Aug 2001, Ross J. Reedstrom wrote: > On Tue, Aug 14, 2001 at 06:51:33AM +, Alan Gutierrez wrote: > > > > Overloading operators? *Please* tell me that this is impossible. > > Well, Alan, overloading operators is sort of important to the user > definable types in postgresql. And any cross-type functionality, actually. ---(end of broadcast)--- TIP 3: 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: [SQL] SQL list table names
alviN wrote: > is it possible to execute an sql query to be able to list the tables's > names? well, you can do it on psql using \dt. but im talking about the SQL > statement, because i want to execute that query from a script. Oh, even *I* know the answer to this one! Run psql with the -E argument to see the SQL used to run \dt. Look at man psql for for info for just: psql -E template1 Alan Gutierrez ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] double linked list
On Thursday 30 January 2003 07:10, Christoph Haller wrote: > I've seen CELKO's reply and find it very useful. > But I cannot find anything about > > > BEGIN ATOMIC > > DECLARE rightmost_spread INTEGER; > > > > SET rightmost_spread > > = (SELECT rgt > > FROM Frammis > > WHERE part = 'G'); > > ... > > Is this PostgreSQL at all? Any hints welcome. Mr Haller No, this is a dialect SQL-92 (SQL-99?) that Mr Celko uses for his examples since his solutions are vendor nutral. He is big on standards, so posting using the standard is his way of boosting them. BEGIN ATOMIC is BEGIN in PG. I am not sure how to declare a variable in PG in normal SQL. I don't do it that often, but when I do I do this: CREATE TEMPORARY TABLE Rightmost_Spread AS SELECT rightmost_spread FROM Frammis WHERE part = 'G'; I wonder what the alterntatives are? Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 3: 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: [SQL] good style?
In article <[EMAIL PROTECTED]>, Rafal Kedziorski wrote: > hi, > > I have 8 tables and this query: > > select u.users_id, m.name as mandant_name, u.login_name, u.password, > u.first_name, u.last_name, u.creation_date, g.name as groups_name, > ae.acl_entry_id, a.name as acl_name, p.name as permission_name > from mandant m, users_2_groups u2g, groups g, users u, permission p, > acl a, acl_entry ae, groups_2_acl_entry g2ae > where m.mandant_id = u.mandant_id and > u2g.groups_id = g.groups_id and > u2g.users_id = u.users_id and > g2ae.groups_id = g.groups_id and > g2ae.acl_entry_id = ae.acl_entry_id and > ae.acl_id = a.acl_id and > ae.permission_id = p.permission_id > > I'm not using JOIN for get this information. would be JOIN a better sql > programming style? faster? Better style, yes. Whitespace would help also. Faster, maybe. If you use join clauses you will be able to take control over your query, specifying what gets joined when. -- Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Matching the MYSQL "Describe " command
I am rather new to Postgres (running 7.4) but I am trying to port some PHP code that has been built to run with mysql. I have got stuck trying to find the equivalent of the Mysql "DESCRIBE ;" SQL statement that lists the columns and type identifiers of a table. I think I am going to do a SELECT on the "pg_attribute" table, but I don't understand what the "attrelid" column of that table refers to. I need to get the table name of the column so that I can restrict the select with a WHERE clause so I assumed it was a key into the pg_class table, but I can't see a column in this table that could potentially correspond and therefore I could join to it. Help -- Alan Chandler http://www.chandlerfamily.org.uk ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Another orderby problem
Just joined the list and have seen in the archive the thread on orderby. But mine seems different. I have a finanancial application with account and transactions which go between accounts (source and destination) but in which one either can be null. I am attempting to read an account and sort all the transactions by date. Here are the transactions id |date| src | dst |description| amount ++---+---+---+ 11 | 2005-06-05 | | Sarah | Sarah Petrol | 27.74 12 | 0005-06-05 | Sarah | | Ben 21st Birthday Pressie |-41 13 | 0005-06-05 | | Sarah | Cash from Mum | 60 14 | 0005-06-08 | | Sarah | Petrol| 27.33 15 | 0005-06-10 | | Sarah | Petrol Allowance |-40 This is the SQL select name, id, transaction.date as tdate, description, -amount as amount from account left join transaction on name=src where name = 'Sarah' union select name, id, transaction.date as tdate, description, amount from account join transaction on name=dst where name ='Sarah' order by tdate asc; name | id | tdate|description| amount ---+++---+ Sarah | 12 | 0005-06-05 | Ben 21st Birthday Pressie | 41 Sarah | 13 | 0005-06-05 | Cash from Mum | 60 Sarah | 14 | 0005-06-08 | Petrol| 27.33 Sarah | 15 | 0005-06-10 | Petrol Allowance |-40 Sarah | 11 | 2005-06-05 | Sarah Petrol | 27.74 (5 rows) I can't figure out why the dates are not in order (see transaction 11 is out of place). for reference the transaction table has the "date" field of type "date" -- Alan Chandler http://www.chandlerfamily.org.uk Open Source. It's the difference between trust and antitrust. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Another orderby problem
On Saturday 14 January 2006 13:09, A. Kretschmer wrote: > am 14.01.2006, um 13:02:48 + mailte Alan Chandler folgendes: > > select name, id, transaction.date as tdate, description, amount > > from account join transaction on name=dst where name ='Sarah' > > order by tdate asc; > > > > name | id | tdate|description| amount > > ---+++---+ > > Sarah | 12 | 0005-06-05 | Ben 21st Birthday Pressie | 41 > > Sarah | 13 | 0005-06-05 | Cash from Mum | 60 > > Sarah | 14 | 0005-06-08 | Petrol| 27.33 > > Sarah | 15 | 0005-06-10 | Petrol Allowance |-40 > > Sarah | 11 | 2005-06-05 | Sarah Petrol | 27.74 > > (5 rows) > > > > I can't figure out why the dates are not in order (see transaction 11 is > > out of place). > > '0005-06-10' < '2005-06-05'. Perhaps you have a wrong year. Thanks Its amazing how long you can stare at something and not see the obvious. -- Alan Chandler http://www.chandlerfamily.org.uk Open Source. It's the difference between trust and antitrust. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
