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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general