Original query:

SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'public' )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

Prettified:

SELECT
  "t"."tablename",
  "indexname",
  "c"."reltuples" AS num_rows,
  pg_size_pretty(pg_relation_size(quote_ident("t"."tablename")::text)) AS table_size,
  pg_size_pretty(pg_relation_size(quote_ident("indexrelname")::text)) AS index_size,
  CASE
    WHEN "indisunique" THEN 'Y'
    ELSE 'N'
  END AS unique,
  "idx_scan" AS number_of_scans,
  "idx_tup_read" AS tuples_read,
  "idx_tup_fetch" AS tuples_fetched
FROM
  "pg_tables" t
  LEFT JOIN "pg_class" c ON ("t"."tablename" = "c"."relname")
  LEFT JOIN (
    SELECT
      "c"."relname" AS ctablename,
      "ipg"."relname" AS indexname,
      "x"."indnatts" AS number_of_columns,
      "idx_scan",
      "idx_tup_read",
      "idx_tup_fetch",
      "indexrelname",
      "indisunique"
    FROM
      "pg_index" x
      JOIN "pg_class" c ON ("c"."oid" = "x"."indrelid")
      JOIN "pg_class" ipg ON ("ipg"."oid" = "x"."indexrelid")
      JOIN "pg_stat_all_indexes" psai ON "x"."indexrelid" = "psai"."indexrelid" AND
                                         "psai"."schemaname" = 'public'
  ) foo ON ("t"."tablename" = "foo"."ctablename")
WHERE
  "t"."schemaname" = 'public'
ORDER BY
  1,
  2