On Tue, Mar 25, 2014 at 2:00 PM, Brian Crowell br...@fluggo.com wrote:
Hello, it's me, a Postgres n00b again. I'm dealing with a query that
scans a rather large table (94,000,000 tuples or so) and just picks
out certain rows and sums them:
select dci.snapshot_time as time,
On Tue, Mar 25, 2014 at 5:59 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Yeah. The weird thing about that is that the nestloop rowcount estimate
isn't the product of the two input rowcounts --- you'd sort of expect an
estimate of 158 given the input-relation sizes. While that's not ipso
facto
Brian Crowell br...@fluggo.com writes:
Here's what I did, though. I collapsed the pl2.current_user view into
pl2.visible_accounts:
===
select
acc.account,
acc.manager,
acc.is_fund
from pl2._visible_accounts_by_rule_set acc
inner join (pl2._users u
left join
On Wed, Mar 26, 2014 at 10:23 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Hm. It's not obvious from here that those give the same results ---
but you probably understand your schema better than the rest of us.
The _users table has a user_id, and a nullable column
impersonating which refers to a
Brian Crowell br...@fluggo.com writes:
Which says to me coalesce has a selectivity.
Well, the point is you're just getting a default selectivity estimate
for the acc.rule_set_id = coalesce(...anything...) condition. The
planner is smarter about plain x = y join conditions: it looks up
the
On Wed, Mar 26, 2014 at 11:43 AM, Tom Lane t...@sss.pgh.pa.us wrote:
In principle I guess we could somehow merge the stats of y and z
when looking at a coalesce(y, z) expression, but I'm not sure
how that would work exactly.
Yeah, I'm not sure there's anything to fix here, either. Just a
Hello, it's me, a Postgres n00b again. I'm dealing with a query that
scans a rather large table (94,000,000 tuples or so) and just picks
out certain rows and sums them:
select dci.snapshot_time as time, round(sum(dci.todays_pl)::numeric,0) as pl
from dbo._pl_data_cache_intraday dci
where
Brian Crowell br...@fluggo.com writes:
The trouble comes when enforcing the dci.account in (...) search
condition: pl2.visible_accounts is a view that determines which
accounts the current user can see, which, depending on who you are,
can be several hundred or none at all. Postgres estimates
Brian Crowell wrote
Hello, it's me, a Postgres n00b again. I'm dealing with a query that
scans a rather large table (94,000,000 tuples or so) and just picks
out certain rows and sums them:
select dci.snapshot_time as time, round(sum(dci.todays_pl)::numeric,0)
as pl
from
On Tue, Mar 25, 2014 at 4:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
So the main estimation error is inside that view, which you didn't
show us :-(
I didn't know which direction you'd want to go with it. :P
The view is like this:
===
create or replace view pl2.visible_accounts
as
select
On Tue, Mar 25, 2014 at 4:12 PM, David Johnston pol...@yahoo.com wrote:
2 - and the one I'd use by default) Use an INNER JOIN
That's where I started, but Postgres is smart enough to know that this
is equivalent to what I'm doing, and still picks the nested loop. I
went to IN in the hopes of
Brian Crowell br...@fluggo.com writes:
Explaining just this view yields:
'Nested Loop (cost=2.77..10.23 rows=2 width=10) (actual time=0.086..0.222
rows=241 loops=1)'
' - Hash Right Join (cost=2.62..5.12 rows=1 width=8) (actual
time=0.064..0.068 rows=1 loops=1)'
' - Index Scan using
12 matches
Mail list logo
