0

I have the following query:

SELECT * 
FROM foo
  LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id 
WHERE foo.entity_id = 11111

foo table is around 8Million rows and foo_history is around 3Billion rows.

Postgres chooses the following plan

Gather  (cost=1000.58..132862.85 rows=9514 width=70) (actual time=462.013..5886.972 rows=14304 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Nested Loop Left Join  (cost=0.58..130911.45 rows=2378 width=70) (actual time=380.838..1450.773 rows=2861 loops=5)
        ->  Parallel Seq Scan on foo  (cost=0.00..93746.74 rows=6 width=24) (actual time=366.122..366.168 rows=6 loops=5)
              Filter: (entity_id = 11111)
              Rows Removed by Filter: 1634858
        ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=3.905..192.879 rows=511 loops=28)
              Index Cond: (foo_id = foo.id)
Planning Time: 1.091 ms
Execution Time: 5891.124 ms

Which requires a seqscan over foo table (8Million row). But when disabling seqscan, postgres chooses a much faster plan:

Nested Loop Left Join  (cost=1.01..148667.31 rows=9514 width=70) (actual time=0.138..27.413 rows=14304 loops=1)
  ->  Index Scan using ix_foo_entity_id on od  (cost=0.43..8.46 rows=24 width=24) (actual time=0.048..0.090 rows=28 loops=1)
        Index Cond: (entity_id = 11111)
  ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=0.027..0.678 rows=511 loops=28)
        Index Cond: (foo_id = foo.id)
Planning Time: 1.135 ms
Execution Time: 28.927 ms

Which use the index over foo.entity_id to find the relevant rows.

The statistics don't seem off to me since they roughly match the ones obtained when running the query (or i'm misreading smth in EXPLAIN ANLYZE). Also random_page_cost is set to 1.1 which should "encourage" postgres to do index scans. So I don't know what's mileading postgres here. Maybe the cost of "Index Scan using ix_foo_history_foo_id" is too high ? But why would that be the case since random_page_cost is set to a reasonable value !

EDIT:

Postgresql version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Tables definitions:

create table foo
(
    id integer default nextval('foo_id_seq'::regclass) not null
        constraint foo_pkey
            primary key,
    entity_id integer not null
        constraint foo_entity_id_fk_entity_id
            references entity
                on delete cascade,
    misc1 integer not null,
    misc2 integer not null,
    misc3 integer
);

create table foo_history
(
    id integer default nextval('foo_history_id_seq'::regclass) not null
        constraint foo_history_pkey
            primary key,
    foo_id integer not null
        constraint foo_history_foo_id_fk_foo_id
            references foo
                on delete cascade,
    history_day integer not null,
    misc1 integer default 0 not null,
    misc2 numeric(10,2) default '0'::numeric not null,
    misc3 numeric(10,2) default '0'::numeric not null,
    misc4 numeric(10,2) default '0'::numeric not null,
    misc5 integer
);

Data and context:

  • I have a table foo, each foo row being attached to an entity. And each foo having it's own history. The goal is to get all foo items and their history that are related to a given entiy.
  • foo.entity_id contains 586972 distinct values
  • Here is a histogram representing how many entities have between 0 and 20 foo items related to them, etc.

foo.entity_id

  • Also I've noticed that the following query
SELECT COUNT(*) FROM (
  SELECT * 
  FROM foo_history 
    LEFT OUTER JOIN foo ON foo.id = foo_history.foo_id 
  WHERE foo.entity_id = 11111
) t

uses the 2nd plan (the fast one)

7
  • Please disclose your version of Postgres for any performance question. (And most other questions, too.) Show (relevant parts of) table definitions (CREATE TABLE script showing data types and constraints) and relevant cardinalities. How many distinct foo.entity_id? Evenly distributed? Distribution of foo_history.foo_id? Why INNER join? (Would seem you'd want all foo, even without rows in foo_history?) Why SELECT *? Tell us what the query shall achieve. – Erwin Brandstetter Mar 15 at 23:20
  • "But when disabling seqscan, postgres chooses a much faster plan" All the data is in cache, because you just got done running the query with the other plan. What if it weren't already in cache? – jjanes Mar 16 at 1:20
  • Your query does not match your plan. The plan has a left join, but the query does not. – jjanes Mar 16 at 2:08
  • @jjanes I've edited the query, it is a LEFT OUTER JOIN indeed. I don't think cache is the problem, even if i run one of the queries multiple times i get the same execution time (which is plan 2 much faster than plan 1) – MG1992 Mar 16 at 7:49
  • @ErwinBrandstetter I've added some more details. As for the INNER JOIN, i've update the query it does use LEFT OUTER JOIN. And for select * it's because i need all the information contained in tables. – MG1992 Mar 16 at 8:26
0

Yes, you are right, the problem is the high cost for the Index Scan using ix_foo_history_foo_id. Observe that it estimates way too many result rows.

Perhaps it helps to

ANALYZE foo_history;

If that alone does not put the estimates right, try

ALTER TABLE foo_history ALTER foo_id SET STATISTICS 1000;
ANALYZE foo_history;

If that does not tilt the scales, perhaps you can INCLUDE some more columns in ix_foo_history_foo_id and replace the SELECT * with only those columns that you really need, so that you get an index-only scan.

10
  • Yes i don't like the idea of changing max_parallel_workers_per_gather since even with the 4 workers allowed the 1st plan is actually slower ! effective_cache_size is 88320MB which seems plenty enough ! – MG1992 Mar 15 at 17:02
  • I see that my assessment was wrong. I have modified the answer. – Laurenz Albe Mar 15 at 17:11
  • Are you refering to "Index Scan using ix_foo_history_foo_id on foo_history foo_history_1 (cost=0.58..6005.63 rows=18849 width=46) (actual time=0.027..0.678 rows=511 loops=28)" ? If so i thought the rows estimate on the left (18849) was to be compared to 511*28 which amount to 15k which is roughly the same. Isn't that so ? For me the estimates seem quite accurate – MG1992 Mar 15 at 18:48
  • 1
    No, the estimate as well as the actual time are per loop. So it is off by a factor of 30 or so. – Laurenz Albe Mar 16 at 0:14
  • This seems to be the answer, i was misreading the estimated count and thought they were accurate. Thanks ! – MG1992 Mar 16 at 14:00

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.