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@ymail.com>
> Komu: "slapo@centrum.sk" <slapo@centrum.sk>, "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
> Dátum: 22.11.2013 20:25
> Predmet: Re: [GENERAL] Puzzling table scan in a CTE
>
"slapo@centrum.sk" <slapo@centrum.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
The Enterprise PostgreSQL Company