go up with actual time, you're fine.
2. You should try to ensure that costs go up linearly with actual time.
3. You should try to ensure that costs are as close as possible to actual time.
4. The number "4".
Jay Levitt
--
Sent via pgsql-performance mailing list (pgsql-performanc
Tom Lane wrote:
Jay Levitt writes:
If the query was more like
select questions.id
from questions
join (
select sum(u.id)
from users as u
group by u.id
) as s
on s.id = questions.user_id
where questions.id = 1;
would you no longer be surprised that it scanned all user rows
Don't hold your breath waiting for that to change. To do what you're
wishing for, we'd have to treat the GROUP BY subquery as if it were an
inner indexscan, and push a join condition into it. That's not even
possible today.
Thanks! Knowing "that's not a thing" helps; we'll just have to rephras
Kevin Grittner wrote:
Merlin Moncure wrote:
Well, this may not fit the OP's 'real' query
Right, if I recall correctly, the OP said it was simplified down as
far as it could be and still have the issue show.
but the inner subquery is probably better written as a semi-join
(WHERE EXISTS).
Kevin Grittner wrote:
Jay Levitt wrote:
I don't get why the GROUP BY in this subquery forces it to scan
the entire users table (seq scan here, index scan on a larger
table) when there's only one row in users that can match:
Are you sure there's a plan significantly fas
Jay Levitt wrote:
And yep! When I do a CREATE TABLE AS from that view, and add an index on
user_id, it works just as I'd like.
Or not. Feel free to kick me back over to pgsql-novice, but I don't get why
the GROUP BY in this subquery forces it to scan the entire users table (seq
Kevin Grittner wrote:
"Kevin Grittner" wrote:
If I had made the scores table wider, it might have gone from the
user table to scores on the index.
Bah. I just forgot to put an index on scores.user_id. With that
index available it did what you were probably expecting -- seq scan
on question
that matches questions.user_id?
Jay Levitt
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Jay Levitt wrote:
Yes, that patch works great! Oddly enough, the workaround now does NOT work;
functions returning SETOF named composite types don't get inlined, but
functions returning the equivalent TABLE do get inlined. Let me know if you
need a failcase, but the bug doesn't actua
Tom Lane wrote:
> Please don't send HTML-only email to these lists.
Oops - new mail client, sorry.
> Anyway, the answer seems to be that inline_set_returning_function needs
> some work to handle cases with declared OUT parameters. I will see
> about fixing that going forward, but in existing re
What other info can I
provide? id is int, gender is varchar(255), and it's happening on
9.0.4...
Tom Lane
November 3, 2011
2:41 PM
Jay Levitt writes:
I'm confused. I have a now-trivial SQL function that, unrestricted, would
scan my whole users tabl
I'm confused. I have a now-trivial SQL function that, unrestricted, would
scan my whole users table. When I paste the body of the function as a
subquery and restrict it to one row, it only produces one row. When I paste
the body of the function into a view and restrict it to one row, it produ
l perform great but it will be difficult to
maintain, and it will be inelegant and a kitten will die. My tools
are CTEs, subqueries, aliases, SQL functions, PL/pgSQL functions, and
views (and other tools?) What optimizations do each of those prevent?
We're on 9.0 now but will happily upgrade to 9.1 if
13 matches
Mail list logo
