0

The system has 4 tables that are joined to get a lot of data about users, this query was turned into a view with 37 columns and a total of ~8 million rows.

Eventually this became slow due to a user having ~1.8 million rows out of the ~8 million, so I decided to make it into a materialized view + add an index on the user_id field.

This materialized view has a single index:

create index ix_testing on testing_performance (user_id);

But querying this table even after 2 minutes it's still not done.

This materialized view is ~4GB in total.

It's a simple query:

select *
from testing_performance tp
where tp.user_id = <user_id>

We cannot reduce the data set (so it has to be all of the users rows, not a small subset of it). I have yet to find a way to make it faster.

explain analyze shows this:

Index Scan using ix_testing on public.testing_performance  (cost=0.43..431210.19 rows=1823850 width=527) (actual time=2.488..19089.553 rows=1829111 loops=1)
  Index Cond: (testing_performance.user_id = <user_id>)
  Buffers: shared read=203325 written=4586
Planning Time: 0.103 ms
Execution Time: 19190.872 ms

This is with track_io_timing on, but data has already been cached by now so it's much faster than above:

 Index Scan using ix_testing on testing_performance bd  (cost=0.43..431210.19 rows=1823850 width=527) (actual time=0.036..1493.887 rows=1829111 loops=1)
   Index Cond: (user_id = <user_id>)
   Buffers: shared hit=3 read=203325 written=11809
   I/O Timings: read=627.655 write=88.767
 Planning Time: 1.459 ms
 Execution Time: 1585.934 ms

EDIT:

With pagination:

select *
from (
    select
        row_number() over (order by tp.creation_date desc) as rn,
        *
    from testing_performance tp
    where user_id = <user_id>
) x
where (x.rn > 50 * coalesce(0,0) and x.rn <= 50 * (coalesce(0,0) + 1));
-- this is a function, I just replace input parameters with real values for pagination

Has the result from explain analyze (this is a cold run, a.k.a run for the first time today):

Subquery Scan on x  (cost=876200.70..935475.83 rows=9119 width=535) (actual time=6822.963..9002.071 rows=50 loops=1)
  Filter: ((x.rn > 0) AND (x.rn <= 50))
  Rows Removed by Filter: 1829061
  Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
  ->  WindowAgg  (cost=876200.70..908118.08 rows=1823850 width=543) (actual time=6822.958..8854.752 rows=1829111 loops=1)
        Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
        ->  Sort  (cost=876200.70..880760.33 rows=1823850 width=535) (actual time=6822.939..7401.405 rows=1829111 loops=1)
              Sort Key: tp.creation_date DESC
              Sort Method: external merge  Disk: 921472kB
              Buffers: shared hit=3 read=125267, temp read=115184 written=115190"
              ->  Index Scan using ix_testing on public.testing_performance tp  (cost=0.43..430943.57 rows=1823850 width=535) (actual time=2.094..4065.285 rows=1829111 loops=1)
                    Index Cond: (tp.user_id = <user_id>)
                    Buffers: shared read=125267
Planning Time: 5.846 ms
Execution Time: 9211.260 ms

On second run:

Subquery Scan on x  (cost=872549.65..931824.78 rows=9119 width=535) (actual time=3957.867..5875.673 rows=50 loops=1)
  Filter: ((x.rn > 0) AND (x.rn <= 50))
  Rows Removed by Filter: 1829061
"  Buffers: shared read=125267, temp read=113393 written=113399"
  ->  WindowAgg  (cost=872549.65..904467.03 rows=1823850 width=535) (actual time=3957.864..5745.112 rows=1829111 loops=1)
"        Buffers: shared read=125267, temp read=113393 written=113399"
        ->  Sort  (cost=872549.65..877109.28 rows=1823850 width=527) (actual time=3957.853..4482.528 rows=1829111 loops=1)
              Sort Key: tp.creation_date DESC
              Sort Method: external merge  Disk: 907144kB
"              Buffers: shared read=125267, temp read=113393 written=113399"
              ->  Index Scan using ix_testing on testing_performance tp  (cost=0.43..430943.57 rows=1823850 width=527) (actual time=0.035..1496.060 rows=1829111 loops=1)
                    Index Cond: (user_id = <user_id>)
                    Buffers: shared read=125267
Planning Time: 0.134 ms
Execution Time: 6070.895 ms
8
  • I assume you need all 37 columns in your result set?
    – J.D.
    Jul 22 at 11:55
  • @J.D. Yes, they have to be returned.
    – Chessbrain
    Jul 22 at 11:55
  • 1
    What happens to the 1.8 million results? Is a human going to read them? Do you do some summarization? Jul 22 at 17:52
  • Your slowest query shown is 20 seconds, many times faster than 2 minutes. Is all the extra time spent sending the data over the network?
    – jjanes
    Jul 22 at 20:14
  • @AndrewSayer It's right now being paginated (with row_num implementation) but still 10+seconds
    – Chessbrain
    Jul 22 at 20:36
1

Your data are not cached, and your index seems bloated. Besides, you didn't show the complete execution plan.

Besides, an index to filter 1.8 million from 8 million will speed up things, but probably not very much.

You should VACUUM the materialized view and set work_mem high.

It seems like most of the time is spent reading the many blocks from disk. If you can afford the down time to run CLUSTER, that may speed up the query somewhat:

CLUSTER testing_performance USING ix_testing;
9
  • It's 1.5s on limit 500. If i let the query go it will take God knows how long (2minutes and it's not done)
    – Chessbrain
    Jul 22 at 9:57
  • You shouldn't show incomplete plans, it confuses. Jul 22 at 10:37
  • I'll let it run then and show the final result
    – Chessbrain
    Jul 22 at 11:11
  • I let explain run fully which took ~20s. Executing the query to return data itself (not explain) ran for over 7 minutes so I killed the query. Index can't be bloated, it was just created after materialized view was created (after insert). Vacuuming a just created materialized view shouldn't result in any improvements (there are no dead tuples, it's a brand new materialized view), but I did a vacuum full anyways on the mat.view and no improvements.
    – Chessbrain
    Jul 22 at 11:26
  • Please set track_io_timing = on and run EXPLAIN (ANALYZE, BUFFERS). Jul 22 at 11:37
0

Now that we have the real requirement (return a page of results for the user) we can suggest ways to make that achievable.

Your current method of pagination is visiting all the rows in the table that match your filters, then sorting them, then returning a range of 50 depending on what page the user is on.

This is very possible to do fast. You would have an index ordered in the appropriate way, start reading the index from where the user left off and stop reading when you have found 50 results.

In order to make sure we don't have any issues due to rows with the same creation_date it's a good idea to include the primary key in the sorting. Our index is then

create index ix_testing on testing_performance (user_id, creation_date desc, pk_col);

User_id to allow it to use your filter, creation_date and pk_col as you will be ordering on them.

Your query should then be

select *
from  testing_performance tp
where user_id = <user_id>
order by user_id, creation_date desc , pk_col
limit 50;

For the first page. Subsequent pages are a little trickier - you need to start reading from the same point you left off from but you can't just do

where user_id = <user_id>
and   creation_date <= <blah> 
and   pk_col > <blah>

As there can be rows with an earlier creation_date but lower pk_col that you do want to read, and you need the pk_col filter so that you don't waste result rows with ones you just read.

It's a little complicated but, to solve this problem (in a way that gets the query planner on your side) you can do two passes

select * from (
select *
from  testing_performance tp
where user_id = <user_id>
and   creation_date  = timestamp <last result seen creation_date>
and pk_col > <last result seen pk_col>
order by user_id, creation_date  desc , pk_col
limit 50
) e
union all
select * from (
select *
from  testing_performance tp
where user_id = <user_id>
and   creation_date < <last result seen creation_date>
order by user_id, creation_date  desc , pk_col
limit 50
) l                                       
order by user_id, creation_date  desc , pk_col
limit 50

This allows you to continue reading from the index from the row you previously got to, and it will stop after 50 results. It's slightly inefficient as if it finds 50 rows in the first subquery (ie you have a lot of rows with the same creation_date) it will still try to find an additional 50 rows with a smaller creation_date. However, this is still HUGELY more optimal than reading all the rows that match your filter, sorting them and then returning a handful - especially when each time the user asks for the next page it would have to do all the reading and sorting again.

I've demoed this method in this DB Fiddle, even with a small demo result set, the differences are huge (1ms down from 96ms)

0

Can you afford the time to create/maintain an INT OR BIGINT column that orders each users rows in the table? This includes reordering all rows for a given user for late arriving data. Then the pagination becomes super simple math.

For example, set up with this:

ALTER TABLE testing_performance ADD user_ordinal BIGINT;

CREATE UNIQUE INDEX testing_performance__user_ordinal_ux ON testing_performance(user_id, user_ordinal);

Single inserts then look like this:

INSERT INTO testing_performance (
  user_id, {columns1-37}, user_ordinal
)
SELECT _user_id, {columns1-37}, 
       MAX(user_ordinal) + 1 -- slow
FROM testing_performance
WHERE user_id = _user_id;

And after every insert of out of order data, do this:

UPDATE testing_performance tp
SET user_ordinal = user_order.ordering
FROM (
  SELECT user_id, 
         ROW_NUMBER() OVER (
           PARTITION BY user_id, ORDER BY id
         ) AS ordering
  FROM testing_performance
) user_order
WHERE tp.user_id = user_order.user_id
AND tp.user_id = _user_id;

During the day you could skip the update to reorder to speed inserts of out of order data, then in a batch job firing every 5 min or so, run updates for users that have a null in the user_ordinal column.

You can also partition based on groups of users (add a grouping column that puts users into buckets and partition on the grouping column. Or, partition say on 50 buckets based on rows per user, and rebalance the partitions on a regular basis. This will ensure that for most users the partition selected will have few rows for all but the massive 1.8m row user.

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.