0

I have a DB with many tables created using the following structure :

CREATE TABLE table1 (id serial primary key, col1 real, col2 real, ..., coln real)

with about 50 columns and 400k rows. The tables are read-only but are fully dropped and recreated once a day (with new values) and are hosted on a HDD.

When I run a simple query based on the PK:

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461 OR table1.id=7618 OR table1.id=45581 OR table1.id=5238;

I get the following QUERY PLAN :

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=28.290..45.165 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=16.748..16.748 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=11.230..11.230 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.832..0.832 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=4.476..4.476 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.208..0.208 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.074 ms
 Execution time: 45.197 ms
(14 rows)

If a run the exact same query again, the QUERY PLAN becomes:

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=0.031..0.036 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=0.024..0.024 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.078 ms
 Execution time: 0.074 ms
(14 rows)

The first question is why in the first query the actual time of the Beatmap Heap Scan is higher than the expected cost ?

Also, is there a configuration that would improve the query performance ?

Many thanks in advance.

PS :due to RAM limitation I can not use pg-warm tool

  • Why you do not use table1.id IN (461,7618,45581,5238) ? – Akina Jan 17 at 9:40
  • "why actual time higher than the expected cost?" - the cost does not indicate any "expected" execution time. It's just a cost value. The second one is faster due to caching - you can see that if you use explain (analyze, buffers, timing) and there is not much you can do about – a_horse_with_no_name Jan 17 at 9:44
0

You need to understand that the costs provided are not absolute values. It basically has no other meaning than helping the planner comparing plans. So you can't compare cost (that has no unit) to actual time (that's given in ms). You can compare costs between two plans (provided by the same postgres server, because it will depend on configuration parameters and sometimes Postgres version).

Then (and I think you figured it out by yourself), the performance difference between first time and second time is that the second time data are cached, so getting them is way faster...

Your query runs in 45ms when data are not cached... That's pretty good and it wouldn't need farther optimization normally. Remember the first rule for optimization is

Don't do it

But I think you simplified your problem here so we could understand it. If your query is way more slower that what you showed and does use OR, remember it's quite expensive (in time and resource). You can always rewrite your query like that :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id IN(461,7618,45581,5238);

or like that :

EXPLAIN ANALYZE
(SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=7618
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=45581
UNION ALL
SELECT col1, col12, col22, col17, col41, col27 FROM table1 table1.id=5238);

Or (like a_horse_with_no_name suggested) with a join on a values clause :

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 INNER JOIN (VALUES (461),(7618),(45581),(5238)) AS t(id) ON table1.id = t.id;

You might find usefull the using explain official documentation page.

  • 1
    Large IN lists are typically faster when rewritten as a JOIN against a values() list rather than many UNIONs (but then those should be UNION ALL) – a_horse_with_no_name Jan 17 at 10:00
  • Yes, that's right! I had forgotten that one and it's really a good one! I'm editting my answer to add both improvements, thanks – Arkhena Jan 17 at 10:01
  • Many thanks to @a_horse_with_no_name and you for your clear explanation. Do you think Index-Only Scans may improve performance ? – Guillaume Jan 18 at 8:01
  • To perform an index-only scan in your case, you need to create an index which has all your columns listed in your select clause. So, there are two counter-sides : - your storage will grow up - your writes operations will be slower You really need to consider those two things before creating any index – Arkhena Jan 18 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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