0

I am using PostgreSQL 9.5.19.

I have a simple SQL statement that is doing a query that returns 1 row on a table that only has 10 rows.

    EXPLAIN
    SELECT * FROM table_a a
    WHERE a.col_a ='a';

Returns

  "Seq Scan on table_a a  (cost=0.00..1.12 rows=1 width=524)"
  "  Filter: ((col_a)::text = 'a'::text)"

Question

Why is it using a Seq scan and not an Index scan?

I thought for only one row, it would do an Index scan.

More info:

I changed the table to have only 2 rows, so that the result set would be 50% of the rows, and it still did a Seq scan. I also changed it to have 100 rows, same thing it was still using a Seq scan.

The table does have the following index (removing the index makes no difference):

    CREATE INDEX idx_a
    ON table_a
    USING btree
    (col_a);

UPDATE:

If I increase the number of rows in the table to 100000, it does a bitmap heap scan.

"Bitmap Heap Scan on table_a a  (cost=4.66..119.19 rows=30 width=25)"

How do I get it to do an index scan?

  • 2
    "on a table that only has 10 rows" - that's the reason. An index scan will never be used on a table with so few rows. – 404 Oct 17 '19 at 08:10
  • Hi 404, thanks for the reply. Why will it never use an index scan on a table with few rows? What is the sort of threshold? –  Oct 17 '19 at 08:13
  • Re your edit: check [this](https://www.db-fiddle.com/f/upNxGhovaFppbdww82hWgi/0) out. If you run that you'll see it does a bitmap heap scan. Now uncomment out the analyze and run it again, and you'll see it does an index scan. – 404 Oct 17 '19 at 08:57
  • Hi 404. I have just tried that, but it still does a heap scan. i.e. commented out `--VACUUM ANALYZE VERBOSE table_a;`. And it does: `"Bitmap Heap Scan on table_a a (cost=4.66..119.19 rows=30 width=25)"` with 1m rows returning one row. –  Oct 17 '19 at 09:54
  • 1
    As to you question "Why will it never use an index scan on a table with few rows?" Postgres always reads full blocks (default size 8K) then excluding an index-only scan using an index scan will typically require 2 physical IOs, read the index to determine where in the heap the data resides and read the heap to get the data. However with small tables a single (multi block) IO often loads the entire table. Then search at CPU speed rather than additional IO (much,much slower). And then there is the % data expected sequentially searching be massively faster than the additional index/heap IO. – Belayer Oct 18 '19 at 00:07

0 Answers0