1

I had a query that was taking way too much time despite having all the indexes I could think of.

Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to another data type because the column from table A is of type varchar while the matching column of table B is of type integer.

I changed my code to use a temporary table in which I insert the rows I need from table A and cast the column from varchar to integer.

It improved the query speed by about a 1000x!

Table zone_site:

CREATE TABLE traitements.zones_sites (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL, 
    origine_id varchar(255) NOT NULL,  -- can't be converted to int
    catégorie varchar(255) NOT NULL,
    horodatage timestamp NOT NULL,
    geom geometry(MultiPolygon,2154),
    précision_contour varchar(100),
    statut varchar(100),
    détails_jsonb text,
    CONSTRAINT enforce_dims_geomloc CHECK (ST_NDims(geom) = 2)
);

CREATE INDEX zones_sites_idx_pkid_site  ON traitements.zones_sites (pkid_site);
CREATE INDEX zones_sites_géométrie ON traitements.zones_sites USING GIST (geom);
CREATE INDEX zones_sites_précision_contour ON traitements.zones_sites (précision_contour);
CREATE INDEX zones_sites_idx_catégorie ON traitements.zones_sites (catégorie);
CREATE INDEX zones_sites_idx_origine ON traitements.zones_sites (origine);
CREATE INDEX zones_sites_idx_statut ON traitements.zones_sites (statut);
ALTER TABLE  traitements.zones_sites
    ADD CONSTRAINT zones_sites_references_sites_candidats FOREIGN KEY (pkid_site)
    REFERENCES traitements.sites_candidats(pkid) ON DELETE CASCADE;

Related question on dba.SE:

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.

Original query:

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

Modified code:

CREATE TEMPORARY TABLE temp_emprises (
    pkid serial PRIMARY KEY,
    pkid_site integer NOT NULL,
    origine varchar(50) NOT NULL, 
    idpk_parcelle integer NOT NULL
);

INSERT INTO temp_emprises (pkid, pkid_site, origine, idpk_parcelle)
SELECT pkid, pkid_site, origine, origine_id::integer 
FROM traitements.zones_sites AS emprises
WHERE emprises.catégorie = 'emprise_site'
AND emprises.précision_contour = 'contour_inconnu'
AND emprises.origine = 'xxxxxxxxx';

CREATE INDEX ON temp_emprises(idpk_parcelle);

SELECT emprises.pkid, emprises.pkid_site, emprises.origine, parcelles.idpar
FROM temp_emprises AS emprises
JOIN parcelles ON parcelles.idpk = emprises.idpk_parcelle;

Is there a better solution?

  • 2
    Please reword the question as question. It might be an interesting one. Provide a CREATE TABLE statement for the original traitements.zones_sites showing data types and constraints. And tell us how you measured factor 1000 exactly. (Including temp table & index creation?) One way or another, there is an even better solution, I am pretty sure. Can the underlying column traitements.zones_sites.origine_id be converted to integer like suggested in my referenced answer? – Erwin Brandstetter Jun 4 '18 at 16:00
  • No, 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. – Darth Kangooroo Jun 5 '18 at 7:16
3

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:

  • Thanks. The 'root cause' is that I have to mix rows of data coming from separate databases of various origins. – Darth Kangooroo Jun 6 '18 at 9:59
  • And parcelles(idpk) is the primary key, so indexed and there is an index on parcelles(idpar). I just tried your solution ... and it is slower than using the temporary table : it takes several minutes (not counting the index creation which takes less than 1"), although the EXPLAIN ANALYZE clearly states it uses the partial index created. – Darth Kangooroo Jun 6 '18 at 10:40
  • The index on parcelles(idpar) is not applicable to this query. Only a multicolumn index with an appended idpar might be useful. The temp table solution should not be faster than the simple query with the optimized index - which certainly shouldn't take minutes. Something's off with your DB setup. Maybe an outdated version of Postgres 9.5? Currently 9.5.13 Is autovacuum running? Can you afford to VACUUM FULL ANALYZE traitements.zones_sites (locks the table while processing)? Same for parcelles. – Erwin Brandstetter Jun 6 '18 at 12:46
  • @ErwinBrandstetter "Since, as you asserted, the column origine_id can be cast to integer", makes you conclude another column, "origine" can be cast to integer too ???? Better have another look at the problem, Erwin, I'm sure you can come up with something better. – Gerard H. Pille Jun 9 '18 at 18:23
  • @GerardH.Pille: Oh, that was a typo. Must be origine_id in the index, too. Thanks for pointing out. – Erwin Brandstetter Jun 9 '18 at 22:27
0

Imagine how much faster it would still be, if you did not create a temporary table, but:

SELECT emprises.pkid, emprises.pkid_site , emprises.origine, parcelles.idpar
    FROM traitements.zones_sites AS emprises
    JOIN parcelles
        ON parcelles.idpk = emprises.origine_id::integer
    WHERE emprises.catégorie = 'emprise_site'
    AND emprises.précision_contour = 'contour_inconnu'
    AND emprises.origine = 'xxxxxxxxx'
  • I tried this one. It was indeed more effective than my original query, reducing the execution time from 24' to 3'. But if at the moment the data in the column origine_id can be casted as integer, it could be non-castable. I only have the certainty that the data in this column can be cast as integer for the rows matching the WHERE clause. So would that create an execution error or not ? Still, using the temporary table is even faster, reducing it down to 6" ! – Darth Kangooroo Jun 5 '18 at 8:25
  • Do you take the creation of the temporary table into account? Don't forget you need to keep it up to date. – Gerard H. Pille Jun 5 '18 at 8:36
  • "it could be non-castable", là, t'as un point. That would mean those sites could never have parcels! I see the sites have "origine" and "origine_id". Is "zones_sites" a view perhaps? Anyway, you'll run into the same problem when you create the temporary table. – Gerard H. Pille Jun 5 '18 at 8:43
  • The 6" takes in account creating the temporary table, yes. Regarding the possibility to cast column origine_id from varchar to integer, it causes no problem when using the temporary table because the cast is during the INSERT, hence after the SELECT has returned a result (which I am sure of, only includes values that can be cast). On the other hand, when not using the temporary table, I am not sure if the content of the whole column will be cast or not during the table JOIN. – Darth Kangooroo Jun 5 '18 at 12:55
  • Your "insert into ... select" clearly has the cast during the select. Do you have a unique index on parcelles.idpk. An index on traitements.zones_sites(catégorie,précision_contour,origine) would also be useful. I can't understand that creating a temporary table plus an index can be faster than a simple select. With "the certainty that the data in this column can be cast as integer for the rows matching the WHERE clause", you can be sure there will be no execution error. – Gerard H. Pille Jun 5 '18 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.