Обсуждение: Plan rows - 1 or many
Hi, I have an application where I would really like to be able to look at en SQL query and answer the question: "Is this query capable of returning more than 1 row?" So basically, queries are divided into 2 categories. Those that look up a single row (if it exists) and those who return a (possible empty) set of rows. (which could also be a set of 1 due to current database content.) Example: It's obvious to see that this query can never return more than one row, no matter what's in the DB: SELECT * FROM mytable WHERE uniquecolumn = 17; But only because we know the column has a unique constraint. Now, unique constraints might be more complex. It could be composite (A,B) where A is a foreign key to another table (Ta) where we can determine that both row referenced by A is unique wrt. the query, so filtering on B and a unique column in Ta would still only ever result in 1 row. SELECT * FROM Ta,Tb WHERE Ta.id = Tb.A AND Tb.B = 17 AND Ta.uniquecolumn = 42; This seems to be about the same as the query planners "plan_rows" try to do. I'm aware that plan_rows is only an estimate and can't answer the question of exactly how many rows in general a query returns, but the question is whether it can be trusted to answer the above more narrow question? I tried look at the source, but could quite figure out if plan_rows were intended to be strict about when it concluded that there was only one row. (like when it evaluated a GROUP BY with only 1 aggregate). Can I conclude that when plan_rows is 1 then there will never be more than 1 row returned by the query? /Peter
Peter Mogensen wrote: > I have an application where I would really like to be able to look at en > SQL query and answer the question: > > "Is this query capable of returning more than 1 row?" > Can I conclude that when plan_rows is 1 then there will never be more > than 1 row returned by the query? I would say no. If the planner estimates one row, that means that it guesses that it will be one or less. And a query that can potentially return many rows can certainly also return only one row or none at all. Yours, Laurenz Albe
On 2014-03-12 09:28, Albe Laurenz wrote: > Peter Mogensen wrote: >> I have an application where I would really like to be able to look at en >> SQL query and answer the question: >> >> "Is this query capable of returning more than 1 row?" > >> Can I conclude that when plan_rows is 1 then there will never be more >> than 1 row returned by the query? > > I would say no. > > If the planner estimates one row, that means that it guesses that > it will be one or less. I think "guesses" is the operative word here. Because, if the planner can actually promise that for the example queries a gave there would be "one or less" rows, then I'm happy. > And a query that can potentially return many rows can certainly > also return only one row or none at all. But that's fine... I have no problem with an answer telling me that this query can return any number of rows. Hmm.. I think my question is answered by posing it in another way: Could "plan rows" tell me whether the result of a given query could be changed by later INSERT statements? And the answer becomes "no", because there's a difference between queries of the type I gave as example and queries using aggregates and GROUP BY. ... for which the planner would also return "plan_rows = 1". However... GROUP BY queries could have their result changed by later INSERT statements. /Peter