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
