0

For a select query on a table with a few million rows, I notice that the performance is much slower with indexing.

I have replicated the problem on an example table with fewer columns:

CREATE TABLE person (
    id integer,
    name varchar(100), 
    age integer, 
    city varchar(20)
) ;

On the suggestion of Laurenz Albe in the comments, I have set track_io_timing = on. Using `EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM person 
    WHERE 
        age BETWEEN 80 AND 90 
        AND 
        city = 'City 5' 

It takes around 1.4s to execute. The plan is as below:

"QUERY PLAN"
"Gather  (cost=1000.00..237856.57 rows=220349 width=77) (actual time=129.511..1359.395 rows=300000 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=16036 read=125869"
"  I/O Timings: read=1701.087"
"  ->  Parallel Seq Scan on person  (cost=0.00..214821.67 rows=91812 width=77) (actual time=46.860..1146.465 rows=100000 loops=3)"
"        Filter: ((age >= 80) AND (age <= 90) AND ((city)::text = 'City 5'::text))"
"        Rows Removed by Filter: 3233333"
"        Buffers: shared hit=16036 read=125869"
"        I/O Timings: read=1701.087"
"Planning:"
"  Buffers: shared hit=3 read=2 dirtied=1"
"  I/O Timings: read=0.375"
"Planning Time: 1.424 ms"
"JIT:"
"  Functions: 6"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 11.377 ms, Inlining 0.000 ms, Optimization 19.323 ms, Emission 120.377 ms, Total 151.077 ms"
"Execution Time: 1419.913 ms"

Now I create an index

create index person_city on person(city);

This has no effect on the query plan and the execution time is almost the same as before.

So I drop that index and create another index:

create index person_age on person(age);

Rerunning the query with this index makes it much slower - the execution time is more than 3x as without the index.


"QUERY PLAN"
"Gather  (cost=15689.52..237548.88 rows=220349 width=77) (actual time=160.029..4357.802 rows=300000 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared read=113786"
"  I/O Timings: read=10923.780"
"  ->  Parallel Bitmap Heap Scan on person  (cost=14689.52..214513.98 rows=91812 width=77) (actual time=76.193..4205.330 rows=100000 loops=3)"
"        Recheck Cond: ((age >= 80) AND (age <= 90))"
"        Rows Removed by Index Recheck: 1318706"
"        Filter: ((city)::text = 'City 5'::text)"
"        Rows Removed by Filter: 266667"
"        Heap Blocks: exact=15652 lossy=22302"
"        Buffers: shared read=113786"
"        I/O Timings: read=10923.780"
"        ->  Bitmap Index Scan on person_age  (cost=0.00..14634.43 rows=1093000 width=0) (actual time=110.459..110.564 rows=1100000 loops=1)"
"              Index Cond: ((age >= 80) AND (age <= 90))"
"              Buffers: shared read=929"
"              I/O Timings: read=7.666"
"Planning Time: 0.391 ms"
"JIT:"
"  Functions: 12"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 8.183 ms, Inlining 0.000 ms, Optimization 5.786 ms, Emission 83.775 ms, Total 97.743 ms"
"Execution Time: 4399.545 ms"

With a 2-column index

create index person_city_age on person(city, age)

it is still as bad.

"QUERY PLAN"
"Gather  (cost=4569.87..228934.30 rows=220348 width=77) (actual time=162.899..5745.851 rows=300000 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared read=113117"
"  I/O Timings: read=14242.342"
"  ->  Parallel Bitmap Heap Scan on person  (cost=3569.87..205899.50 rows=91812 width=77) (actual time=57.395..5436.325 rows=100000 loops=3)"
"        Recheck Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))"
"        Rows Removed by Index Recheck: 1494927"
"        Heap Blocks: exact=15466 lossy=22826"
"        Buffers: shared read=113117"
"        I/O Timings: read=14242.342"
"        ->  Bitmap Index Scan on person_city_age  (cost=0.00..3514.79 rows=220348 width=0) (actual time=65.691..65.797 rows=300000 loops=1)"
"              Index Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))"
"              Buffers: shared read=260"
"              I/O Timings: read=3.092"
"Planning Time: 0.275 ms"
"JIT:"
"  Functions: 6"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 10.720 ms, Inlining 0.000 ms, Optimization 13.682 ms, Emission 78.843 ms, Total 103.245 ms"
"Execution Time: 5795.385 ms"

When I increase the work_mem to 1024 MB, it skips the gather node at the top. The performance is still not as good as sequential scan with 4 MB.

"QUERY PLAN"
"Bitmap Heap Scan on person  (cost=14689.49..175721.94 rows=220348 width=77) (actual time=293.036..2035.050 rows=300000 loops=1)"
"  Recheck Cond: ((age >= 80) AND (age <= 90))"
"  Filter: ((city)::text = 'City 5'::text)"
"  Rows Removed by Filter: 800000"
"  Heap Blocks: exact=112857"
"  Buffers: shared read=113786"
"  I/O Timings: read=1202.640"
"  ->  Bitmap Index Scan on person_age  (cost=0.00..14634.40 rows=1092997 width=0) (actual time=197.170..197.261 rows=1100000 loops=1)"
"        Index Cond: ((age >= 80) AND (age <= 90))"
"        Buffers: shared read=929"
"        I/O Timings: read=5.402"
"Planning Time: 0.528 ms"
"JIT:"
"  Functions: 4"
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 9.242 ms, Inlining 0.000 ms, Optimization 8.242 ms, Emission 42.427 ms, Total 59.911 ms"
"Execution Time: 2081.710 ms"

What's going on and what can I do about it? Appreciate any tips!

10
  • It could be too low work_mem, but even more likely are caching effects. If you set track_io_timing = on and use EXPLAIN (ANALYZE, BUFFERS), you will see more. Jan 26 at 6:54
  • Thanks! Did that and updated the query plan in the question.
    – ahron
    Jan 26 at 7:06
  • @LaurenzAlbe I increased the work_mem to 1024 MB and it is faster! Down to 2s from over 4s. However, still slower than the seq scan.
    – ahron
    Jan 26 at 7:10
  • Looks like PostgreSQL's optimizer is off here. Perhaps you can play with the cost parameters. You are aware that a two-column index on (city, age) would solve your problem? Jan 26 at 7:22
  • 1
    I cannot believe that not having to read and process the 800000 rows that get removed by the filter won't make a real difference. Jan 26 at 8:27

2 Answers 2

2

Create test data:

CREATE UNLOGGED TABLE person (
    id integer,
    name varchar(100), 
    age integer, 
    city varchar(20)
) ;
INSERT INTO person 
SELECT n, 'Person '||n, random()*100, 'City '||((random()*10)::INTEGER) 
FROM generate_series(1,10000000) n;
VACUUM ANALYZE person;

Without further information in the question, I used 10 cities and uniform distribution for age, which is not exactly realistic.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM person
    WHERE age BETWEEN 80 AND 90 AND city = 'City 5';
 Gather  (cost=1000.00..159257.47 rows=118108 width=29) (actual time=4.609..233.192 rows=109664 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=16132 read=57398
   ->  Parallel Seq Scan on person  (cost=0.00..146446.67 rows=49212 width=29) (actual time=3.072..208.706 rows=36555 loops=3)
         Filter: ((age >= 80) AND (age <= 90) AND ((city)::text = 'City 5'::text))
         Rows Removed by Filter: 3296779
         Buffers: shared hit=16132 read=57398
 Planning:
   Buffers: shared hit=4 read=1 dirtied=1
 Planning Time: 0.309 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.550 ms, Inlining 0.000 ms, Optimization 1.085 ms, Emission 7.817 ms, Total 10.452 ms
 Execution Time: 237.753 ms

CREATE INDEX ON person( city, age );

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM person
    WHERE age BETWEEN 80 AND 90 AND city = 'City 5';

 Bitmap Heap Scan on person  (cost=1918.31..80524.42 rows=118108 width=29) (actual time=16.905..149.152 rows=109664 loops=1)
   Recheck Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))
   Heap Blocks: exact=57224
   Buffers: shared read=57322 written=14
   ->  Bitmap Index Scan on person_city_age_idx  (cost=0.00..1888.79 rows=118108 width=0) (actual time=9.524..9.524 rows=109664 loops=1)
         Index Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))
         Buffers: shared read=98
 Planning:
   Buffers: shared hit=18 read=1
 Planning Time: 0.386 ms
 Execution Time: 152.448 ms

As expected the index does not help much. This is because the rows are quite small (so a page contains many rows) and they are randomly distributed in the table, so there is an almost 100% chance that each page will contain one row that satisfies the condition. So the bitmap scan reads pretty much the entire table, and provides very little advantage.

The exact same thing happens in your question: if you look at the number of buffers read, you'll notice it's the same for all the queries.

CLUSTER enables a better on-disk row layout:

CLUSTER person USING person_city_age_idx ;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM person
    WHERE age BETWEEN 80 AND 90 AND city = 'City 5';

 Bitmap Heap Scan on person  (cost=1918.31..80524.42 rows=118108 width=29) (actual time=5.233..49.662 rows=109664 loops=1)
   Recheck Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))
   Heap Blocks: exact=807
   Buffers: shared read=905
   ->  Bitmap Index Scan on person_city_age_idx  (cost=0.00..1888.79 rows=118108 width=0) (actual time=5.166..5.167 rows=109664 loops=1)
         Index Cond: (((city)::text = 'City 5'::text) AND (age >= 80) AND (age <= 90))
         Buffers: shared read=98
 Planning:
   Buffers: shared hit=12 read=2 dirtied=1
 Planning Time: 0.494 ms
 Execution Time: 52.441 ms

This makes the bitmap scan a lot more efficient, and fewer pages are read. But CLUSTER is slow and locks the table. You may also consider a covering index.

Now, why the index scan is so spectacularly slow in your query, I have no idea. The table uses 0.6GB so it should be completely cached in RAM, it shouldn't even read from disk. I'd recommend profiling IO.

1
  • Yes, I think this is a very reasonable explanation.
    – ahron
    Jan 26 at 16:24
0

Can you create 1 index with both fields relevant to the query? It seems that the query is not benefitting from the index because the index isn't providing specificity meeting the query criteria.

Try

CREATE INDEX idx_Query_Person_AgeCity on person(Age,City)

Check the term "SARGABLE" - try cover the query with an index.

5
  • The order of the columns is wrong. Jan 26 at 7:23
  • Elaborate? The query specifies "WHERE age BETWEEN 80 AND 90 AND city = 'City 5'"
    – Alocyte
    Jan 26 at 7:25
  • Precisely. You have to put the column that is compared with = first, otherwise the index scan has to scan more index entries than necessary. Jan 26 at 7:29
  • I'd rewrite both query and recreate index to reference the columns in the same order, as you say "=" first however i do know the query optimiser filtered on age, then city. "Filter: ((age >= 80) AND (age <= 90) AND ((city)::text = 'City 5'::text))"
    – Alocyte
    Jan 26 at 7:45
  • 1
    The order in which you write the conditions is irrelevant. Jan 26 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.