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 <kgri...@ymail.com> Komu: "sl...@centrum.sk" <sl...@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
"sl...@centrum.sk" <sl...@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 <http://www.enterprisedb.com> The Enterprise PostgreSQL Company
