Optimizing query with type casting
Proper solution

Your comment (which I added to your question) discloses the root of the problem:


  Unfortunately, the column traitements.zones_sites.origine_id cannot be
  of type integer because it holds identifier from several origins, some
  of them not being integers.


A roughly normalized DB design would avoid to mix data of different kind in the same column. Then your true integer values could be in a column of type integer and the rest in (a) text column(s). And there wouldn't be a problem to begin with.

While stuck with your current design

Since, as you asserted, the column origine_id can be cast to integer for the selection of rows in your query, I suggest this much faster alternative: create a partial expression index:

CREATE INDEX zones_sites_idx_origine_part_int ON traitements.zones_sites ((origine_id::int))
WHERE catégorie = 'emprise_site'
AND   précision_contour = 'contour_inconnu'
AND   origine = 'xxxxxxxxx';


If the cast in your INSERT into the temp table is guaranteed to work, then so is this index.

Slightly modified query:

SELECT z.pkid, z.pkid_site, z.origine, p.idpar
FROM   traitements.zones_sites z
JOIN   parcelles               p ON p.idpk = z.origine_id::int
WHERE  z.catégorie = 'emprise_site'
AND    z.précision_contour = 'contour_inconnu'
AND    z.origine = 'xxxxxxxxx';


Since the partial index also supports your query perfectly, this should make your current solutions look like slow motion in comparison. And it's simpler and more reliable, too.

The only other index you need is one on parcelles(idpk).
Since the only other column you need from parcelles is idpar, if that table is big and write-load is low so you can get index-only scans out of it, an (additional) multicolumn index on parcelles(idpk, idpar) would make it even faster.

And faster, yet, in the upcoming Postgres 11 with true covering indexes with an INCLUDE column:

Related answer with code example:


Does a query with a primary key and foreign keys run faster than a query with just primary keys?