Re: Sequential scan faster than index

On Tue, 21 Feb 2023 at 22:30, Arthur Ramsey <arthur(dot)ramsey(at)code42(dot)com> wrote:
>
> I'm trying to figure out why a sequential scan is out performing. I've tried psql 13.7, psql14.6 and REINDEX. The REINDEX didn't help. This is on an RDS instance that's a db.m5.large (2 * vCPU, 8 GB memory) with 200 storage on io1 with 10000 provisioned IOPS. I restarted the RDS instance in between each test.

You'll probably see something more meaningful if you SET
track_io_timing = on; and EXPLAIN (ANALYZE, BUFFERS). There are
plenty of genuine reasons for seq scans being faster than scanning an
index. Scanning large indexes may require large amounts of random I/O,
especially when the indexes are larger than the RAM in the machine.
I've no idea how large these indexes are, but the row counts in your
plans seem to indicate that you're dealing with something in the order
of 30 million rows. If your disk latency is high then scanning an
entire large index that's mostly not in RAM is going to not perform
great. You'll likely see that I/O reads are taking longer with the
index scan plan than with the seq scan plan. That might change if you
were to do something like CLUSTER the partition by the index you're
using here. However, clustering on that index might make various
other queries slower. It's also important to read the documents about
CLUSTER and understand what it does and what it does not do too.

The fact that the planner wants to use the seq scan and if you disable
enable_seqscan and it becomes slower, then that's an indication that
the planner is likely making the correct choice. The problem might
just be with your expectations of index scan performance.

David