sql - 大表查询

Question

我有以下查询 -

SELECT  n.fname
       ,ii.render AS practitioner_npi
       ,n.address AS address1
       ,substring(n.postal,0,6) AS zip
       ,substring(n.postal,6,4) AS zip4
       ,ii.count AS count
  FROM
  (
        SELECT render, count(*) AS count
        FROM dx sl
        JOIN annual caq
        ON DATE_TRUNC('quarter', date_of_service::date) >= caq.start
        JOIN entities n
        ON sl.render = n.npi
        WHERE dx_cd IN (
          SELECT DISTINCT dx_cd
          FROM dx_per_code pc
          JOIN bucket bac
          ON pc.code = bac.hcpccode
          WHERE
          bucketname = 'something'
          AND dx_rank BETWEEN 1 AND 5
        )
        AND n.npi_type = '1'
        GROUP BY render
  )
   ii
  JOIN npi n ON n.npi = ii.render
  LEFT JOIN taxonomy t ON t.code = n.taxonomy
ORDER BY ii.count DESC;

dx 表没有任何索引，包含大约 8B 条记录。此查询当前需要 20 分钟才能运行。我可以进行哪些索引/优化以使其运行得更快？

标签： sqlpostgresql