Sorry for the delay, but I didn't have access to the database during the
weekend.
Here's the output of "explain (analyze, buffers)":
http://explain.depesz.com/s/scC
I'm also curious why it actually seems to touch the table assuming there are
output columns which I haven't defined anywhere (see previously posted plan).
Cheers. :-)
Peter Slapansky
______________________________________________________________
Od: Elliot <yields.falseh...@gmail.com>
Komu: <sl...@centrum.sk>, <pgsql-general@postgresql.org>
Dátum: 22.11.2013 19:58
Predmet: Re: [GENERAL] Puzzling table scan in a CTE
CC: "Elliot"
On 2013-11-22 12:49, sl...@centrum.sk <sl...@centrum.sk> wrote:Thanks for the
suggestion.
I've tried it with seqscan set to off, but there's still a bitmap heap scan
going on:
http://explain.depesz.com/s/zIJl <http://explain.depesz.com/s/zIJl>
I have random_page_cost set to 1.5 at the moment, as the database is on a solid
state disk.
Every user has a parent, but not every parent has a child.
The number of rows returned by the query is 17 at the moment.
It would be even less for a child tree of other users, usually 0 to 3, and the
plan remains the same in those cases.
The table itself has only slightly below 5000 rows right now. It's not a lot,
but it seems too many to go for a table scan for just 17 rows.
Could it be that the planner cannot estimate the possible match count because
of the CTE?
Can you do "explain (analyze, buffers)"? I'm wondering if your entire table is
in a very small number of pages, possibly all on just one page, in which case a table
scan makes sense. The plan with seqscan off has just a tiny bit higher estimated cost,
and it ran 1.5ms slower - that difference could be noise, but I'm thinking that all it's
doing is an extra index page read without eliminating any table data page reads (under
the assumption that the table is all in a single page).
