PostgreSQL not using index during count(*)

index-tuningperformancepostgresqlpostgresql-9.3postgresql-performance

I have a COUNT(*) query in PostgreSQL that runs often, and looks like:

SELECT COUNT(*) 
  FROM customer 
 WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

This query takes between 30-60 seconds to run and searches millions of records.

EXPLAIN ANALYZE shows it's doing a sequential scan, so I created the index:

CREATE INDEX customer_by_source ON customer (source_id)
WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16);

However, EXPLAIN ANALYZE still shows it's doing a sequential scan and isn't using the index.

How do I speed up this query and make it use the index?

Edit: My Postgres version is 9.3.3. The table has about 20 million records, divided pretty evenly among each source_id, of which another 5 aren't included in the list.

Best Answer

If I interpret your added information correctly, you have 21 distinct source_id with roughly 1 million rows each (divided pretty evenly among each source_id). That means your query counts roughly 3/4 of the whole table. An index typically can't buy much either way.

Test with EXPLAIN (just EXPLAIN is enough for this purpose) to see the estimated cost for each variant:

EXPLAIN SELECT .... -- your query

And take note of all cost= numbers in the output.

Then (only in your session):

SET enable_seqscan = off;

And repeat the procedure. Once more after:

SET enable_indexscan = off;

This should tell you why Postgres chooses a sequential scan. Typically, if your index is substantially smaller than the table (index entries substantially smaller than table rows and / or substantially fewer rows) and if conditions for an index-only scan are met, Postgres will choose that route - unless your cost settings are seriously out of touch with the reality of your setup.

Most importantly, the visibility map has to show that whole pages are visible to all transactions. VACUUM updates the visibility map after writes. Read details on the linked Wiki page.

You can try your query once more immediately after running VACUUM ANALYZE customer;.

If index-only scans are not possible, a sequential scan will most probably be faster than a bitmap index scan, meaning that your index would be of no use.

Update for Postgres 9.6

There was an important restriction for partial indexes, improved in Postgres 9.6. The release notes:

Allow use of an index-only scan on a partial index when the index's WHERE clause references columns that are not indexed (Tomas Vondra, Kyotaro Horiguchi)

For example, an index defined by CREATE INDEX tidx_partial ON t(b) WHERE a > 0 can now be used for an index-only scan by a query that specifies WHERE a > 0 and does not otherwise use a. Previously this was disallowed because a is not listed as an index column.

Related Solutions

Postgresql being slow on count distinct for dates

There is a trick with distinct to get it fast using index, that you can try. It involves creating a function looking like that:

CREATE OR REPLACE FUNCTION small_distinct(IN tablename character varying, IN fieldname character varying, IN sample anyelement DEFAULT '1800-01-01'::date)
  RETURNS SETOF anyelement AS
$BODY$
BEGIN
   EXECUTE 'SELECT '||fieldName||' FROM '||tableName||' ORDER BY '||fieldName
      ||' LIMIT 1'  INTO result;
   WHILE result IS NOT NULL LOOP
      RETURN NEXT;
      EXECUTE 'SELECT '||fieldName||' FROM '||tableName
         ||' WHERE '||fieldName||' > $1 ORDER BY ' || fieldName || ' LIMIT 1'
         INTO result USING result;
   END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Then create an index on the column you want to count distinct, and select small_distinct('yourtable', 'yaddate'); should return you the distinct values you want, without the need to read the table.

Try it, be beware, I'm not sure it will work right out of the box, as I quickly adapted it from a varchar function.

Postgresql – postgres: index to SELECT strings like ‘%foo%’;

The index should work as is. As pointed out by @jjanes, you just needed to run ANALYZE. However, I suggest you modify the index. The expression in your definition is not useful.

CREATE INDEX foo_idx ON intentions ((data LIKE '%foo%')) WHERE data NOT LIKE '%foo%';

The expression is always FALSE and useless noise. Either simplify to the slightly cheaper equivalent index:

CREATE INDEX foo_idx ON intentions ((FALSE)) WHERE data NOT LIKE '%foo%';

Or, better yet, put something useful there. Since each index entry is going to allocate MAXALIGN (typically 8 bytes) for data anyway, and the overhead per index row is another 12 bytes (and the default fillfactor for btree indexes is 90%) you might as well put the index to good use and help other queries. If you have any other queries that might use the same partial index?

If you put something worth up to 8 bytes (like one or two int4 columns), the index retains exactly the same size and performance, but offers more possible use cases. If (one of) the column(s) is involved in updates, you have some additional maintenance cost. Still, if you have anything useful, use it. Any additional column that's used in a JOIN / WHERE or ORDER BY clause. Or column(s) that enable index-only scans:

CREATE INDEX foo_idx ON intentions (<useful_column(s)>)
WHERE data NOT LIKE '%foo%';

Best Answer

Update for Postgres 9.6

Related Solutions

Postgresql being slow on count distinct for dates

Postgresql – postgres: index to SELECT strings like ‘%foo%’;

Related Question