PostgreSQL 9.3 – Why Indexes Are Bigger Than Their Tables

performancepostgresqlpostgresql-9.3postgresql-performance

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables.

Sample output:

 database_name | database_size |                          table_name                          | table_size | indexes_size | total_size
---------------+---------------+--------------------------------------------------------------+------------+--------------+------------
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_sends"                       | 57 MB      | 131 MB       | 189 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_started"                      | 17 MB      | 39 MB        | 56 MB
 foo_12345 | 412 MB        | "foobar_dev_12345"."fact_mobile_stopped"                      | 16 MB      | 35 MB        | 51 MB

I'm running the following query to get the table and index sizes.

SELECT
    table_catalog AS database_name,
    pg_size_pretty(pg_database_size(current_database())) As database_size,
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_catalog,
        pg_database_size(current_database()) AS database_size,
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name, table_catalog
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Is my query correct? What would cause indexes to be bigger?

Best Answer

Possible reasons:

Numerous and probably overlapping indexes on the table; have a look with \d
Bloat due to high update churn can sometimes affect indexes more than tables, depending on update patterns. Examine the size of each individual index to see if it makes sense.
GiST indexes, if used, can be quite large

Unlike what I originally thought this is not an issue with TOAST out-of-line storage not being counted, since pg_table_size includes TOAST tables.

Note that if you're concerned about index bloat and decide to REINDEX some of all of the involved indexes, consider setting a non-default FILLFACTOR first if the table is subject to lots of updates (or inserts+deletes). Otherwise you'll take a write performance hit because the index doesn't have any space to insert new values so it'll force lots of page splits and be less efficiently structured.

Related Solutions

PostgreSQL – Return Unique Column Combinations Based on Where Clause

The execution plan shown does not seem to match the big SELECT DISTINCT query because the Sort and Unique steps are missing. Anyway you are correct than when retrieving ~50% of a table, index don't help. The best strategy is a big sequential scan of the main table and only fast hardware helps with that.

For the 2nd part of the question:

How would I go about selecting only the unique combinations of adjacent columns? Is this too complicated a task to perform through a database query? Would it speed up the query?

To remove duplicate combinations of adjacent columns, the structure of the resultset should be changed so that each output row has only one couple of adjacent columns along with their corresponding dimensions in the parallel coordinates graph. Well, except that the dimension for the 2nd column is not necessary since it's always the dimension for the other column plus one.

In one single query, this could be written like this:

WITH logs as (
  SELECT log_time_mapped, syslog_priority_mapped, 
     operation_mapped, message_code_mapped, protocol_mapped, 
     source_ip_mapped, destination_ip_mapped, 
     source_port_mapped, destination_port_mapped, 
     destination_service_mapped, direction_mapped, 
     connections_built_mapped, connections_torn_down_mapped, 
     hourofday_mapped, meridiem_mapped 
  FROM firewall_logs_mapped 
  WHERE operation = 'Built')
SELECT DISTINCT 1, log_time_mapped, syslog_priority_mapped FROM logs
UNION ALL
SELECT DISTINCT 2, syslog_priority_mapped, operation_mapped FROM logs
UNION ALL
SELECT DISTINCT 3, operation_mapped, message_code_mapped FROM logs
UNION ALL
...etc...
SELECT DISTINCT 14,  hourofday_mapped, meridiem_mapped FROM logs
;

The first SELECT DISTINCT subquery extracts the lines to draw between dimensions 1 and 2, the next subquery between dimensions 2 and 3, and so on. DISTINCT eliminates duplicates, so the client side doesn't have to do it. The UNION ALL concatenates the results without any further processing.

However it's a heavy query and it's dubious that it would be any faster than what you're already doing.

The WITH subquery is likely to gets slowly materialized on disk, so it might be interesting to compare the execution time with this other form repeating the same condition:

SELECT DISTINCT 1, log_time_mapped, syslog_priority_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
UNION ALL
SELECT DISTINCT 2, syslog_priority_mapped, operation_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
UNION ALL
SELECT DISTINCT 3, operation_mapped, message_code_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
...etc...
;

Postgresql size of Slave is bigger than Master size

SOLVED. It was not DB problem. du command showed correct size of DB. Problem was in my BackUP system. It helds descriptors of deleted files. Restart of backup service solved problem

lsof | grep '(deleted)' shows deleted files process ID in second column.

Best Answer

Related Solutions

PostgreSQL – Return Unique Column Combinations Based on Where Clause

Postgresql size of Slave is bigger than Master size

Related Question