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 ?
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"