'Query is too slow and not using existing index

I have a simple query that takes quite long to load (~ 20min.)

Here's my query plan: https://explain.dalibo.com/plan/RZm

And this is the query in question:

SELECT count(*) AS count,
    dd.country,
    COALESCE(dd.state, 'unknown'::character varying) AS state,
    COALESCE(dd.district, 'unknown'::character varying) AS district,
    dd.region,
    df.facilityname,
    dde.date,
    df.tenantid,
    ds.systemmanufacturer,
        CASE
            WHEN dd.country IS NULL OR dd.country::text = 'unknown'::text THEN false
            ELSE true
        END AS country_detected,
        CASE
            WHEN dd.state IS NULL OR dd.state::text = 'unknown'::text THEN false
            ELSE true
        END AS state_detected,
        CASE
            WHEN dd.district IS NULL OR dd.district::text = 'unknown'::text THEN false
            ELSE true
        END AS district_detected
   FROM fact_entriesexits fe
     JOIN dim_licenseplate dd ON dd.key = fe.licenseplatekey
     JOIN dim_facility df ON df.key = fe.facilitykey
     JOIN dim_date dde ON dde.key = fe.datekey
     JOIN dim_systeminterface ds ON ds.key = fe.systeminterfacekey
  WHERE fe.devicetype = 1
  GROUP BY df.tenantid, dd.region, dd.country, dd.state, dd.district, df.facilityname, ds.systemmanufacturer, dde.date, fe.licenseplatekey;

I do have an index on devicetype from the fe table. But it is never being used?

postgresql


Solution 1:[1]

Apart from caching more data in RAM or getting faster disks, a nested loop join may perform better:

CREATE INDEX ON fact_entriesexits (licenseplatekey, devicetype)
   INCLUDE (facilitykey, datekey, systeminterfacekey);

VACUUM fact_entriesexits;

That could get you an index-only scan.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions