[HACKERS] subselect in the column list
If we have a query of the form: Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75 In SQL, if a subselect is present in the column-list, is the result set of this subselect considered to be a part of a single tuple returned from the outer query, or does the result set of the whole query look like a cross product of results of outer and inner query thanks -Amit PS: sorry i tried to send this email earlier and accidentally pressed send before completing the email ... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Behavior of subselects in target lists and order by
If we have a query of the form: Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75 In the above query there is a subselect in the target list and the ORDERBY has an ordinal number which indicates order by column 1. Does this mean that the above query will return all results from T1 that match p3 =75 and all results from T2 that match p2 = T1.p1 for every match on T1 and order them all by the first column of T1 and T2 ? basically i am trying to determine if the order by clause has effect only on the tuples of the outer select or both the outer and inner select. Or the results returned by the inner select are treated as if they are part of a single tuple which includes the tuple from table T1 ? Is this an implementation specific behaviour or it conforms to the SQL standard ...? thanks -Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cursor behavior
It seems that the task of fetching next n results without moving the cursor seems like too complicated to implement for any query that has even a little bit of complication in it... --- On Wed, 12/21/11, Robert Haas robertmh...@gmail.com wrote: From: Robert Haas robertmh...@gmail.com Subject: Re: [HACKERS] Cursor behavior To: amit sehas cu...@yahoo.com Cc: pgsql-hackers@postgresql.org Date: Wednesday, December 21, 2011, 8:43 AM On Thu, Dec 15, 2011 at 4:15 PM, amit sehas cu...@yahoo.com wrote: I had a question about the cursor internals implementation. When you Fetch next 'n' results without moving the cursors, is this kind of functionality implemented by firstly executing the whole query and then moving the cursor over the results, or are the movements done on active database lookups, moving forward and backward... I think it depends on the query. For example, I believe that a query involving writeable CTEs will be run to completion before returning any results, but I believe that a seqscan will not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Cursor behavior
HI, I had a question about the cursor internals implementation. When you Fetch next 'n' results without moving the cursors, is this kind of functionality implemented by firstly executing the whole query and then moving the cursor over the results, or are the movements done on active database lookups, moving forward and backward... it seems that the cursor implementation functions evaluate the whole query and then return results as requested, it would seem to be too difficult to otherwise support a functionality that returns next 10 results without moving the cursors... we need to determine this since in our case we are dealing with a query that returns millions of records, and we would rather the cursor did not evaluate the whole query upfront... any help is greatly appreciated... thanks -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] array behavior
If a field(attribute) in a type is declared to be an array. Then is it always the case that the array will be packed into a single tuple that resides in the database. There is the TOAST mechanism for oversized tuples but that is still considered to be a single tuple. Is there any circumstance in which an attribute which is an array will be broken up into individual tuples which are somehow associated with the main tuple. Such as if the array happens to have 5000 elements and the tuple will become quite large if these are packed within a single tuple and additionally it may have undesirable performance impact if the queries are not even interested in seeing the array when fetching the object ? thanks -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] query execution question
We are making some performance measurements, we are trying to determine query execution behavior. Lets say we have 4 tables T1, T2, T3 and T4 and the query has the form: select * from T1, T2, T3, T4 where (T1.a = T2.b and T2.c = T3.d T3.e = T4.f) where a,b,c,d,e,f are properties of the respective tables. Lets say that the cost based optimizer determines that the order of the joins should be T1.a=T2.b followed by T2.c = T3.d followed by T3.e = T4.f the question we have is during query execution are the joins evaluated completely one by one in that order, or the first join is evaluated completely and generates an intermediate table which is then utilized to perform the next jointhis means that for such a query we will need space for all the intermediate tables, which if they are very large tables as they are in our case can significantly alter the cost of the operations... thanks -Ashish -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] optimization histograms
HI, for the histograms for cost based optimization, is there a rule of thumb on how often to rebuild them? They are obviously not being continuously updated...what is the state of the art in this area, do all the other databases also end up with stale statistics every now and then and have to keep rebuilding the stats? thanks -Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
