Database optimizers don’t see the future. They make decisions based on statistics, estimates, and cost models — and most of the time, those decisions work remarkably well.
But what if the optimizer says:
“The Sequential Scan is cheaper.”
…and reality says:
“The Index Only Scan is almost twice as fast.”
That is exactly what I wanted to investigate.
For this experiment, I created a deliberately skewed PostgreSQL dataset containing 100 million rows, where almost every row has the same customer_type and only a single row represents a rare value.
This creates the perfect environment to challenge the optimizer’s assumptions and compare estimated cost with actual execution time.
The question is simple:
Does PostgreSQL’s estimated cost really tell us which execution plan will be faster?
The results were surprising.
Not always.
And the difference between what PostgreSQL estimated and what actually happened reveals an important lesson about reading and troubleshooting execution plans.
1.Creating the Test Table
postgres=# CREATE TABLE tbl_test (
id BIGINT,
customer_type VARCHAR(20),
city VARCHAR(30),
status VARCHAR(20)
);
CREATE TABLE
The important column for this experiment is customer_type.
I wanted to create an extremely skewed data distribution:
· 99,999,999 rows → REGULAR
· 1 row → VIP
The other columns were added to make the table more representative of a real-world table.
2. Generating 100 Million Rows
I used PostgreSQL’s generate_series() to generate 100 million rows:
postgres=#INSERT INTO tbl_test (id, customer_type, city, status)
SELECT
gs,
CASE
WHEN gs = 100000000 THEN 'VIP'
ELSE 'REGULAR'
END,
CASE (gs % 10)
WHEN 0 THEN 'Babol'
WHEN 1 THEN 'Amol'
WHEN 2 THEN 'Behshahr'
WHEN 3 THEN 'MarziKola'
WHEN 4 THEN 'Bandpay'
WHEN 5 THEN 'DerazKesh'
WHEN 6 THEN 'Sari'
WHEN 7 THEN 'Rasht'
WHEN 8 THEN 'Zanjan'
ELSE 'Tel Aviv'
END,
CASE
WHEN gs % 100 < 95 THEN 'ACTIVE'
WHEN gs % 100 < 99 THEN 'INACTIVE'
ELSE 'SUSPENDED'
END
FROM generate_series(1, 100000000) AS gs;
INSERT 0 100000000
This is useful because it allows us to compare two very different situations.
3. Creating the Index
I created a B-tree index on customer_type:
postgres=# CREATE INDEX idx_customer_type ON tbl_test(customer_type);
CREATE INDEX
Then I checked the size of both objects:
postgres=# SELECT 'TABLE' AS object_type, 'tbl_test' AS object_name, pg_relation_size('tbl_test') / 1024 / 1024 AS size_mb UNION ALL SELECT 'INDEX', 'idx_customer_type', pg_relation_size('idx_customer_type') / 1024 / 1024;
object_type | object_name | size_mb
-------------+-------------------+---------
TABLE | tbl_test | 5918
INDEX | idx_customer_type | 660
(2 rows)
The index is significantly smaller than the table. This will become important later.
4. Collecting PostgreSQL Statistics
Before asking the optimizer to make decisions, I collected statistics:
postgres=# ANALYZE VERBOSE tbl_test;
INFO: analyzing "public.tbl_test"
INFO: "tbl_test": scanned 30000 of 757576 pages, containing 3959968 live rows and 0 dead rows; 30000 rows in sample, 99999224 estimated total rows
INFO: finished analyzing table "postgres.public.tbl_test"
avg read rate: 96.315 MB/s, avg write rate: 0.010 MB/s
buffer usage: 874 hits, 29255 reads, 3 dirtied
WAL usage: 10 records, 3 full page images, 10583 bytes, 0 buffers full
system usage: CPU: user: 0.59 s, system: 0.07 s, elapsed: 2.37 s
ANALYZE
5. Querying the Common Value
Now let’s ask PostgreSQL to count the REGULAR customers:
postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
QUERY PLAN
------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1383572.41..1383572.42 rows=1 width=8)
-> Gather (cost=1383572.20..1383572.41 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=1382572.20..1382572.21 rows=1 width=8)
-> Parallel Seq Scan on tbl_test (cost=0.00..1278406.17 rows=41666413 width=0)
Filter: ((customer_type)::text = 'REGULAR'::text)
(6 rows)
PostgreSQL chooses a Sequential Scan instead of the index because it estimates that reading most of the table directly will be cheaper than using the index.
Now, let’s execute the query and see how long PostgreSQL actually takes:
postgres=# SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
count
----------
99999999
(1 row)
Time: 15362.798 ms (00:15.363)
Execution time: 15.36 seconds to count nearly 100 million rows.
6.Forcing PostgreSQL to Use the Index
So far, PostgreSQL had a clear preference: Sequential Scan.
But I wanted to see what would happen if I took that option away.
For investigation purposes, I disabled sequential scans:
postgres=# SET enable_seqscan = off;
SET
Then I asked PostgreSQL to show me the plan again:
postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=1610210.39..1610210.40 rows=1 width=8)
-> Gather (cost=1610210.17..1610210.38 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=1609210.17..1609210.18 rows=1 width=8)
-> Parallel Index Only Scan using idx_customer_type on tbl_test (cost=0.57..1505044.14 rows=41666413 width=0)
Index Cond: (customer_type = 'REGULAR'::text)
(6 rows)
PostgreSQL switched to a Parallel Index Only Scan And now we see something very interesting. The estimated cost increased:
Sequential Scan ≈ 1,383,572
Index Only Scan ≈ 1,610,210
In other words, PostgreSQL’s own cost model still considers the Index Only Scan to be the more expensive option.
But I wasn’t interested in what PostgreSQL thought would happen.
I wanted to know what would actually happen.
So I executed the query:
--Forcing PostgreSQL to Use the Index
postgres=# SET enable_seqscan = off;
SET
postgres=# SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'REGULAR';
count
----------
99999999
(1 row)
Time: 7883.189 ms (00:07.883)
Execution time: ~7.88 seconds
And this is where the experiment gets interesting.
PostgreSQL’s cost model says:
Index Only Scan → More expensive
But the real execution says:
Index Only Scan → 7.88 seconds
This is the exact gap I wanted to investigate:
Estimated cost and actual execution time are not the same thing.
Metric Sequential Scan Index Only Scan
Estimated Cost 1.38M 1.61M
Execution Time 15 sec 7.88 sec
Actual Result Slower 1.9× faster
7. What Happens With the Rare Value?
Now let’s ask for the opposite value: customer_type = ‘VIP’
postgres=# explain SELECT COUNT(*) FROM tbl_test WHERE customer_type = 'VIP';
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=4.59..4.60 rows=1 width=8)
-> Index Only Scan using idx_customer_type on tbl_test (cost=0.57..4.58 rows=1 width=0)
Index Cond: (customer_type = 'VIP'::text)
(3 rows)
This is exactly what we would expect.
There is only one VIP row in 100 million rows.
Using the index allows PostgreSQL to locate that row without scanning the entire table. So we have two extreme cases:
REGULAR → 99,999,999 rows → Sequential Scan
VIP → 1 row → Index Only Scan
This demonstrates the importance of selectivity.
8. Removing the Aggregate
To make the comparison more realistic, I removed the COUNT(*) aggregate and selected the indexed column directly.
First, with sequential scans enabled:
postgres=# SET enable_seqscan = on;
SET
postgres=# EXPLAIN (ANALYZE, BUFFERS)SELECT customer_type FROM tbl_test WHERE customer_type = 'REGULAR';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_test (cost=0.00..2007576.40 rows=100000032 width=8) (actual time=2.577..22438.130 rows=99999999.00 loops=1)
Filter: ((customer_type)::text = 'REGULAR'::text)
Rows Removed by Filter: 1
Buffers: shared hit=1192 read=756384
Planning Time: 0.169 ms
Execution Time: 26854.922 ms
(6 rows)
PostgreSQL chose a Sequential Scan. Execution time: 26.9 seconds
Now I forced PostgreSQL to use the index:
postgres=# SET enable_seqscan = off;
SET
EXPLAIN (ANALYZE, BUFFERS)SELECT customer_type FROM tbl_test WHERE customer_type = 'REGULAR';
SET
Time: 0.302 ms
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_customer_type on tbl_test (cost=0.57..2088385.13 rows=100000032 width=8) (actual time=0.163..9471.775 rows=99999999.00 loops=1)
Index Cond: (customer_type = 'REGULAR'::text)
Heap Fetches: 0
Index Searches: 1
Buffers: shared read=84203
Planning Time: 0.170 ms
Execution Time: 13451.658 ms
(7 rows)
Execution time: 13.45 seconds.
Sequential Scan Index Only Scan
Execution Time 26.9 sec 13.45 sec
Buffers Read 756,384 84,203
The Index Only Scan was roughly 2× faster, despite PostgreSQL estimating its cost as higher.
This is where the difference between planner cost and real-world execution becomes impossible to ignore.
Oracle Database: A Different Optimizer Decision
I repeated a similar experiment in Oracle using the same logical data distribution.
SQL> CREATE TABLE tbl_test (
2 id NUMBER(19),
3 customer_type VARCHAR2(20),
4 city VARCHAR2(30),
5 status VARCHAR2(20)
6 );
Table created
BEGIN
FOR i IN 0..99 LOOP
INSERT /*+ APPEND */ INTO tbl_test
(id, customer_type, city, status)
SELECT
i * 1000000 + LEVEL,
CASE
WHEN i = 99 AND LEVEL = 1000000 THEN 'VIP'
ELSE 'REGULAR'
END,
CASE MOD(i * 1000000 + LEVEL, 10)
WHEN 0 THEN 'Babol'
WHEN 1 THEN 'Amol'
WHEN 2 THEN 'Behshahr'
WHEN 3 THEN 'MarziKola'
WHEN 4 THEN 'Bandpay'
WHEN 5 THEN 'DerazKesh'
WHEN 6 THEN 'Sari'
WHEN 7 THEN 'Rasht'
WHEN 8 THEN 'Zanjan'
ELSE 'Tel Aviv'
END,
CASE
WHEN MOD(i * 1000000 + LEVEL, 100) < 95
THEN 'ACTIVE'
WHEN MOD(i * 1000000 + LEVEL, 100) < 99
THEN 'INACTIVE'
ELSE 'SUSPENDED'
END
FROM dual
CONNECT BY LEVEL <= 1000000;
COMMIT;
END LOOP;
END;
/
SQL> CREATE INDEX idx_customer_type ON tbl_test(customer_type);
Index created
The Oracle object sizes were:
SQL> select segment_name,segment_type ,bytes/1024/1024 from dba_segments where owner='VAHID';
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
-------------------- ------------------ ---------------
TBL_TEST TABLE 3744
IDX_CUSTOMER_TYPE INDEX 2112
Now let’s run the same test in Oracle:
set autotrace traceonly explain
SQL> SELECT COUNT(*) FROM vahid.tbl_test WHERE customer_type = 'REGULAR';
Execution Plan
----------------------------------------------------------
Plan hash value: 1445291348
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 72420 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_CUSTOMER_TYPE | 107M| 1225M| 72420 (1)| 00:00:03 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUSTOMER_TYPE"='REGULAR')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Oracle chooses an INDEX FAST FULL SCAN!
Despite almost every row matching REGULAR, Oracle scans the index because the query only needs COUNT(*).
In the actual test, this plan completed in approximately 10 seconds.
SQL> SELECT COUNT(*) FROM vahid.tbl_test WHERE customer_type = 'REGULAR';
COUNT(*)
----------
99999999
Elapsed: 00:00:10.11
Top comments (0)