Re: [GENERAL] Puzzling table scan in a CTE

I apologise for the late response.
 
I've increased "effective_cache_size" to 50% and tried again - no change.
Afterwards, I've increased "cpu_tuple_cost" from 0.02 to 0.05 and tried again - no change.
 
What is most curious to me is that I think the initial result set is very small, so any JOINs on it should be pretty selective and the number of results should be pretty small with each scan: 1 to 10 from about 4000 rows. I might be missing something, but the row estimates are accurate.
The actual number of loops should be between 1 and 3 right now, as the tree doesn't go any deeper - a user has at most two parents right now, although that might change later.
 
Thank you. :-)
 
Peter Slapansky
 
______________________________________________________________
> Od: Kevin Grittner <kgrittn(at)ymail(dot)com>
> Komu: "slapo(at)centrum(dot)sk" <slapo(at)centrum(dot)sk>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
> Dátum: 22.11.2013 20:25
> Predmet: Re: [GENERAL] Puzzling table scan in a CTE
>
"slapo(at)centrum(dot)sk" <slapo(at)centrum(dot)sk> wrote:

> I have a recursive CTE where a table scan occurs, even though
> there doesn't seem to be a good reason for it.

Do you have effective_cache_size set to 50% to 75% of machine RAM?
Do you have cpu_tuple_cost set to between 0.03 and 0.05?  If not,
do changes to these settings help?

--
Kevin Grittner
EDB: http://www.enterprisedb.com <http://www.enterprisedb.com>
The Enterprise PostgreSQL Company