3

Context

I am using Postgres v13 hosted in GCP Cloud SQL. The database partitions most tables by a tenant id.

Structure

The query is more complex than this but I can create a minimal reproducible example from the essence of these three tables:

create table student (
  id text not null primary key,
  grade_id int not null
);

create table student_snapshot (
  student_id text not null,
  created_at timestamp not null,
  grade_id int not null,
  primary key (student_id, created_at)
);

create table test (
  id text not null primary key,
  student_id text not null,
  student_snapshot_date timestamp not null,
  foreign key (student_id, student_snapshot_date) references student_snapshot (student_id, created_at)
);

There are students and when they take a test we take a snapshot of the student to understand their demographics at the time the test was taken. There isn't an FK back to student because we want to keep the historic data when the student is deleted. The real example uses partitioned tables but this example uses normal ones for simplicity.

Query

I need to join the 3 tables together in a query like the following:

select t.*, st.grade_id as current_grade_id, snp.grade_id as snapshot_grade_id
from student st
join test t
  on t.student_id = st.student_id
join student_snapshot snp
  on snp.student_id = t.student_id
  and snp.created_at = t.student_snapshot_date

Issue

The query was taking < 1s but can now take upwards of 2hrs after a recent infusion of data into the database (~50% increase). The performance did not degrade linearly as the volume increased. It appears to have changed the normal query plan which now uses a Nested Loop. It appears to be making this choice because it expects the row count to be 2 when it is really closer to 90k. I find it interesting that when I leave off the and snp.created_at = t.student_snapshot_date condition that the query plan has a reasonable expectation of the number of rows returned and does not choose a Nested Loop.

Here is the query plan for the full query:

QUERY PLAN                                                                                                                                                                                                                                                 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Nested Loop  (cost=2044.29..6432.12 rows=2 width=10) (actual time=65.097..1904659.897 rows=72902 loops=1)                                                                                                                                                  |
  Output: st.student_id                                                                                                                                                                                                                                    |
  Buffers: shared hit=34879380                                                                                                                                                                                                                             |
  ->  Hash Join  (cost=2043.88..5769.94 rows=2 width=20) (actual time=41.050..955.542 rows=90631 loops=1)                                                                                                                                                  |
        Output: t.student_id, snp.student_id                                                                                                                                                                                                               |
        Hash Cond: ((t.student_id = snp.student_id) AND (t.student_snapshot_date = snp.created_at))                                                                                                                                                        |
        Buffers: shared hit=3015                                                                                                                                                                                                                           |
        ->  Seq Scan on public."test" t  (cost=0.00..3046.31 rows=90631 width=18) (actual time=0.010..169.664 rows=90631 loops=1)                                                                                 |
              Output: t.id, t.student_id, t.student_snapshot_date|
              Buffers: shared hit=2140                                                                                                                                                                                                                     |
        ->  Hash  (cost=1342.55..1342.55 rows=46755 width=18) (actual time=39.342..39.343 rows=46755 loops=1)                                                                                                                                              |
              Output: snp.student_id, snp.created_at                                                                                                                                                                                                       |
              Buckets: 65536  Batches: 1  Memory Usage: 3069kB                                                                                                                                                                                             |
              Buffers: shared hit=875                                                                                                                                                                                                                      |
              ->  Seq Scan on public."student_snapshot" snp  (cost=0.00..1342.55 rows=46755 width=18) (actual time=0.018..13.942 rows=46755 loops=1)                                                                |
                    Output: snp.student_id, snp.created_at                                                                                                                                                                                                 |
                    Buffers: shared hit=875                                                                                                                                                                                                                |
  ->  Index Only Scan using "student_key" on public."student" st  (cost=0.41..331.08 rows=1 width=10) (actual time=12.086..21.000 rows=1 loops=90631)           |
        Output: st.program_id, st.organization_id, st.student_id                                                                                                                                                                                           |
        Index Cond: (st.student_id = t.student_id)                                                                                                                                                                                                         |
        Heap Fetches: 74061                                                                                                                                                                                                                                |
        Buffers: shared hit=34876365                                                                                                                                                                                                                       |
Planning:                                                                                                                                                                                                                                                  |
  Buffers: shared hit=40                                                                                                                                                                                                                                   |
Planning Time: 1.063 ms                                                                                                                                                                                                                                    |
Execution Time: 1904722.476 ms                                                                                                                                                                                                                             |

Note the discrepancy between rows and actual rows.

If I remove the date match then I get this:

QUERY PLAN                                                                                                                                                                                                                                           |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Hash Join  (cost=4254.08..8568.10 rows=92784 width=10) (actual time=114.921..219.856 rows=74332 loops=1)                                                                                                                                             |
  Output: st.student_id                                                                                                                                                                                                                              |
  Hash Cond: (t.student_id = st.student_id)                                                                                                                                                                                                          |
  Buffers: shared hit=3744                                                                                                                                                                                                                           |
  ->  Seq Scan on public."test" t  (cost=0.00..3046.31 rows=90631 width=10) (actual time=0.010..12.670 rows=90631 loops=1)                                                                                  |
        Output: t.id, t.student_id, t.student_snapshot_date|
        Buffers: shared hit=2140                                                                                                                                                                                                                     |
  ->  Hash  (cost=3689.98..3689.98 rows=45128 width=20) (actual time=114.737..114.739 rows=36995 loops=1)                                                                                                                                            |
        Output: st.student_id, snp.student_id                                                                                                                                                                                                        |
        Buckets: 65536  Batches: 1  Memory Usage: 2391kB                                                                                                                                                                                             |
        Buffers: shared hit=1604                                                                                                                                                                                                                     |
        ->  Hash Join  (cost=1720.82..3689.98 rows=45128 width=20) (actual time=33.975..95.906 rows=36995 loops=1)                                                                                                                                   |
              Output: st.student_id, snp.student_id                                                                                                                                                                                                  |
              Hash Cond: (snp.student_id = st.student_id)                                                                                                                                                                                            |
              Buffers: shared hit=1604                                                                                                                                                                                                               |
              ->  Seq Scan on public."student_snapshot" snp  (cost=0.00..1342.55 rows=46755 width=10) (actual time=0.030..13.741 rows=46755 loops=1)                                                          |
                    Output: snp.student_id, snp.created_at, snp.program_id, snp.organization_id, snp.first_name, snp.last_name, snp.gender_id, snp.grade_id, snp.date_of_birth, snp.race, snp.academic, snp.zip, snp.state_id                        |
                    Buffers: shared hit=875                                                                                                                                                                                                          |
              ->  Hash  (cost=1169.81..1169.81 rows=44081 width=10) (actual time=31.513..31.514 rows=43262 loops=1)                                                                                                                                  |
                    Output: st.student_id                                                                                                                                                                                                            |
                    Buckets: 65536  Batches: 1  Memory Usage: 2287kB                                                                                                                                                                                 |
                    Buffers: shared hit=729                                                                                                                                                                                                          |
                    ->  Seq Scan on public."student" st  (cost=0.00..1169.81 rows=44081 width=10) (actual time=0.015..10.926 rows=43262 loops=1)                                                              |
                          Output: st.student_id                                                                                                                                                                                                      |
                          Buffers: shared hit=729                                                                                                                                                                                                    |
Planning:                                                                                                                                                                                                                                            |
  Buffers: shared hit=40                                                                                                                                                                                                                             |
Planning Time: 1.042 ms                                                                                                                                                                                                                              |
Execution Time: 223.517 ms                                                                                                                                                                                                                           |

Note the better row estimate and the lack of a Nested Loop.

What I have tried

I tried creating extended statistics to increase the selectibility:

CREATE STATISTICS "test_st_snp_pk" ON student_id, student_snapshot_date FROM "test";
CREATE STATISTICS "st_snp_pk" ON student_id, created_at FROM "student_snapshot";

I tried increasing the stats collection on the relevant columns:

alter table "test" alter student_id set statistics 500;
alter table "test" alter student_snapshot_date set statistics 500;
alter table "student_snapshot" alter student_id set statistics 500;
alter table "student_snapshot" alter created_at set statistics 500;

I tried clustering on a parallel indexes for test and student_snapshot to see if it would find more hits if they were sorted the same:

cluster "test" using "test_student_id_student_snapshot_date_idx";
cluster "student_snapshot" using "student_snapshot_pkey";

I ran analyze after each but had the same plane each time.

The only thing I have done that has worked is to materialize part of the query but that isn't as efficient as it should be. But even that failed when I started adding back in the peripheral tables that were part of the more complex query.

with test_w_snap as materialized (
  select t.*, snp.grade_id
  from test t
  join student_snapshot snp
    on snp.student_id = t.student_id
    and snp.created_at = t.student_snapshot_date
)
select t.*, st.grade_id as current_grade_id, t.grade_id as snapshot_grade_id
from student st
join test_w_snap t
  on t.student_id = st.student_id

I'd much rather find a way to feed the query planner the right statistics to properly estimate and pick the right query plan.

What can I do to get the query planner to have a more accurate estimate?

Update Though I am still not happy with the row estimates, I found the root issue. The student_snapshot table used text for student_id while the other two used citext. We recently updated student_snapshot to also be citext so that they all matched. I don't understand why it used a better query plan back when they mismatched but in comparing query plans I saw that it was doing snp.student_id = t.student_id::text. When I altered the query to snp.student_id::text = t.student_id::text it became fast again. Seems a bit counterintuitive since the conversion should be less efficient, but it led to a much better query plan even though the row estimate was still terribly off. It uses a Parallel Hash Join instead of a Nested Loop. Sorry I didn't provide enough context for this earlier.

4
  • Can you include some queries to suitably populate your tables using, for example, generate_series and random()?
    – jjanes
    Sep 21, 2022 at 20:36
  • You didn't define a table named 'rating', and student doesn't have a column named student_id.
    – jjanes
    Sep 21, 2022 at 20:38
  • @jjanes sorry, that should be test. I'll update it. I'll also add the solution I ended up finding.
    – bygrace
    Sep 23, 2022 at 14:03
  • 1
    I am assuming that the two different join conditions are both quite selective, but both eliminate mostly the same set of rows, so in combination they are much less selective than if they were independent. When doing the cross type comparison, it can't estimate the selectivity based on real data (I think this depends on versions) so has to adopt a default estimate, which happens to work out better.
    – jjanes
    Sep 23, 2022 at 17:31

1 Answer 1

2

Your diligent research made clear that the problem is a correlation between your two join conditions. Since PostgreSQL doesn't have cross-table correlation statistics (now that would be a game changer!), you'll probably have to force the hand of the optimizer:

WITH cte AS MATERIALIZED (
   select r.*, st.grade_id as current_grade_id, snp.grade_id as snapshot_grade_id,
          snp.created_at, r.student_snapshot_date
   from student st
   join rating r
     on r.student_id = st.student_id
   join student_snapshot snp
     on snp.student_id = r.student_id
)
SELECT *
FROM cte
WHERE created_at = student_snapshot_date;

You probably don't want to SELECT *, but I don't know the columns of rating, so you will have to fix the query accordingly. Also, if some column names occur in more than one table, you might have to use aliases where necessary.

1
  • It would be nice if they had cross-table correlation statistics! I had thought about using pg_hint_plan but have been trying to avoid it. I added an update with some context I didn't have earlier. Came down to an inefficient conversion making things faster.
    – bygrace
    Sep 23, 2022 at 14:15

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.