Hi all I also tried to change the values of join_collapse_limit and rom_collapse_limit to higher values than default: 12, 50 or even 100, with no improvement on the query plan.
Is this a typical behavior, or is there something particular in my query that causes this big difference between the raw query and the view with WHERE ? Regards Michaël 2017-08-18 18:46 GMT+02:00 kimaidou <kimai...@gmail.com>: > Hi all, > > I have come across a unexpected behavior. > You can see full detail on an issue on the QGEP project in Github : > https://github.com/QGEP/QGEP/issues/308#issuecomment-323122514 > > Basically, we have this view with some LEFT JOIN : > http://paste.debian.net/982003/ > > We have indexes on some fields ( foreign keys, and a GIST index for the > PostGIS geometry field) > If I use the raw SQL defining the view, and add a WHERE clause like: > > WHERE "progression_geometry" && st_makeenvelope(1728327. > 03249295568093657,8240789.26074041239917278,1728608. > 10987572139129043,8240958.16933418624103069,3949) > > the query plan is "as expected", as it is using the spatial index (and > others too). This query gets 100 lines from a "main" table containing 20000 > lines (and child tables having more). It is pretty fast and "low cost" > See the query plan: > https://explain.depesz.com/s/6Qgb > > When we call the WHERE on the view: > > EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) > SELECT * > FROM "qgep"."vw_qgep_reach" > WHERE "progression_geometry" && st_makeenvelope(1728327. > 03249295568093657,8240789.26074041239917278,1728608. > 10987572139129043,8240958.16933418624103069,3949) > > > The query plan is "wrong", as PostgreSQL seems to consider it should do a > seq scan on the tables, and only afterwards filter with the WHERE: > https://explain.depesz.com/s/wXV > > The query takes about 1 second instead of less than 100ms. > > Do you have any hint on this kind of issue ? > > Thanks in advance > > Regards, > > Michaël > >
