7

I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows.

          Column          |            Type             | Modifiers
--------------------------+-----------------------------+-----------
 transaction_id           | text                        | not null
 network_merchant_name    | text                        |
 network_merchant_id      | text                        |
 network_merchant_mcc     | integer                     |
 network_merchant_country | text                        |
 issuer_country           | text                        |
 merchant_id              | text                        |
 remapped_merchant_id     | text                        |
 created_at               | timestamp without time zone |
 updated_at               | timestamp without time zone |
 remapped_at              | timestamp without time zone |
Indexes:
    "mapped_transactions_pkey" PRIMARY KEY, btree (transaction_id)
    "ix_mapped_transactions_remapped_at" btree (remapped_at NULLS FIRST)

Here is the query I am trying to execute.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST
LIMIT 10000;

Here is the query plan:

    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=327049.374..327345.341 rows=10000 loops=1)
   Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
   I/O Timings: read=146625.381 write=59.637
   ->  Index Scan using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190862.91 rows=107145960 width=146) (actual time=327049.364..327339.402 rows=10000 loops=1)
         Buffers: shared hit=574937 read=210425 dirtied=356 written=4457
         I/O Timings: read=146625.381 write=59.637
 Planning time: 0.125 ms
 Execution time: 327348.322 ms
(8 rows)  

I don't understand why it takes so much time when there is an index on the remapped_atcolumn.

On the other hand if I order in reverse it is fast.

SELECT *
FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST
LIMIT 10000;

and the plan is:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..1511.67 rows=10000 width=146) (actual time=0.020..9.268 rows=10000 loops=1)
   Buffers: shared hit=1802
   ->  Index Scan Backward using ix_mapped_transactions_remapped_at on mapped_transactions  (cost=0.57..16190848.04 rows=107145866 width=146) (actual time=0.018..4.759 rows=10000 loops=1)
         Buffers: shared hit=1802
 Planning time: 0.080 ms
 Execution time: 11.561 ms
(6 rows)

Can someone please help me how I can improve the performance of the first query?

Updates

  1. I solved this by rebuilding the table and reindexing the data. VACUUM FULL ANALYZE was not an option because the table was being used and I did not want to lock it.

  2. The performance of the index is deteriorating very fast. I rebuilt the index 7 hours ago and the performance was good. Now the queries answer in about 10s. Please note that that this table is write heavy. How can I make the index fast? Do I have to re-index the table frequently? There are no deletions in this table but a lot of updates.

16
  • Hmmm... this is a very interesting question which has me stumped! I'm giving it a +1 for putting in table definitions and plans (unusual for a first post!). How many rows in your table? How many remapped_at are NULL? 9ms to > 4 mins - wow!
    – Vérace
    Commented Nov 4, 2017 at 19:22
  • Like I have mentioned in the post there are 60 million rows in the table. There should not be any NULL in remapped_at but it is possible to insert NULL for legacy purposes. At the time when i ran the query there were 0 NULL in tat column.
    – Zeeshan
    Commented Nov 4, 2017 at 19:41
  • Ooops (re. 60M :-) )! There are some very knowledgeable PostgreSQL guys around here - but it is Sat. evening/afternoon! Try editing your question on Monday at 13:00 UTC, better responses then! - it'll go back into the active questions list! Let me know via a comment (put @Vérace in the body). I'll bring it to the chat room - I think it's worth pursuing!
    – Vérace
    Commented Nov 4, 2017 at 20:11
  • 1
    @EvanCarrollQWERHJKL the test case posted worked for me. VACUUM FULL ANALYZE is not possible as this will lock the table and it is being used by other processes (reads and writes). I might be able to make a copy of it and run some more tests.
    – Zeeshan
    Commented Nov 5, 2017 at 1:35
  • 2
    @Zeeshan: please add that as an answer and accept that answer so that your question is marked as resolved.
    – user1822
    Commented Nov 6, 2017 at 13:54

2 Answers 2

1

I can't confirm with PostgreSQL 9.5. So this may be a regression. Sample data,

CREATE TABLE mapped_transactions(remapped_at)
AS
  SELECT CASE WHEN x::int % 42 = 0 THEN null ELSE x::int END
  FROM generate_series( 1, 40e6 )
    AS gs(x);

CREATE INDEX ON mapped_transactions (remapped_at NULLS FIRST);
VACUUM ANALYZE mapped_transactions;

Here we do NULLS FIRST and it works,

EXPLAIN ANALYZE
SELECT *
FROM mapped_transactions
ORDER BY remapped_at ASC NULLS FIRST LIMIT 10000;
                                                                                     QUERY PLAN                                                                                      
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.069..4.308 rows=10000 loops=1)
   ->  Index Only Scan using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.067..2.740 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.232 ms
 Execution time: 5.017 ms
(5 rows)

Trying DESC NULLS LAST

EXPLAIN ANALYZE
SELECT * FROM mapped_transactions
ORDER BY remapped_at DESC NULLS LAST LIMIT 10000;
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..260.24 rows=10000 width=4) (actual time=0.073..4.429 rows=10000 loops=1)
   ->  Index Only Scan Backward using mapped_transactions_remapped_at_idx on mapped_transactions  (cost=0.56..1038716.81 rows=40000016 width=4) (actual time=0.071..2.865 rows=10000 loops=1)
         Heap Fetches: 0
 Planning time: 0.114 ms
 Execution time: 5.137 ms
(5 rows)

You may have to work up a test case. It's not evident what you're experiencing.

1
  • this test case worked for me and I get the similar response time as you posted. I am still not sure what is wrong with my original table.
    – Zeeshan
    Commented Nov 5, 2017 at 1:33
0

I ran some more tests and it seems like this solution is not going to work for me. The index is constantly being degraded over time and if I don't re-index (or drop and create again) the queries I want to run will get slower and slower. There are ways to create the index without blocking other reads/writes but at this time I am not going to go with them because they are not easily scalable. I cannot solve this problem with the knowledge I have so I am going to go with another approach. This approach uses an index with a column that is constant and it never gets updated. Thanks everyone for helping me out.

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.