Speed up slow Postgres query with window functions

Question

I'm trying to optimise a query, as the query generated by my ORM (Django) is causing timeouts. I've done everything possible within the ORM to run it as one query, so now I wanted to know if there are any Postgres tricks that can speed things up.

The database contains 1m+ and growing relationships (id, source and target) which I need to filter to exclude connections where the source doesn't appear at least 2 times.

This is the current query - and the list of "target" ids can grow which leads to exponential slowdowns.

SELECT * FROM
(SELECT
    "source",
    "target",
    count("id") OVER (PARTITION BY "source") AS "count_match"
FROM
    "database_name"
WHERE
    ("database_name"."target" IN (123, 456, 789))
) AS temp_data WHERE "temp_data"."count_match" >= 2

I've read about VIEWS and temporary TABLES but that seems like a lot of setup and tear-down for a one-off query.

EDIT: Further info and tests on higher memory

Result of EXPLAIN ANALYSE:

Subquery Scan on alias_test  (cost=622312.29..728296.62 rows=1177604 width=24) (actual time=10245.731..18019.237 rows=1604749 loops=1)
  Filter: (alias_test.count_match >= 2)
  Rows Removed by Filter: 2002738
  ->  WindowAgg  (cost=622312.29..684136.48 rows=3532811 width=20) (actual time=10245.687..16887.428 rows=3607487 loops=1)
        ->  Sort  (cost=622312.29..631144.32 rows=3532811 width=20) (actual time=10245.630..12455.796 rows=3607487 loops=1)
              Sort Key: database_name.source
              Sort Method: external merge  Disk: 105792kB
              ->  Bitmap Heap Scan on database_name  (cost=60934.74..238076.96 rows=3532811 width=20) (actual time=352.529..1900.162 rows=3607487 loops=1)
                    Recheck Cond: (target = ANY ('{5495502,80455548,10129504,2052517,11564026,1509187,1981101,1410001}'::bigint[]))
                    Heap Blocks: exact=33716
                    ->  Bitmap Index Scan on database_name_target_426d2f46_uniq  (cost=0.00..60051.54 rows=3532811 width=0) (actual time=336.457..336.457 rows=3607487 loops=1)
                          Index Cond: (target = ANY ('{5495502,80455548,10129504,2052517,11564026,1509187,1981101,1410001}'::bigint[]))
Planning time: 0.288 ms
Execution time: 18318.194 ms

Table structure:

    Column     |           Type           |                                     Modifiers
---------------+--------------------------+-----------------------------------------------------------------------------------
 created_date  | timestamp with time zone | not null
 modified_date | timestamp with time zone | not null
 id            | integer                  | not null default nextval('database_name_id_seq'::regclass)
 source        | bigint                   | not null
 target        | bigint                   | not null
 active        | boolean                  | not null
Indexes:
    "database_name_pkey" PRIMARY KEY, btree (id)
    "database_name_source_24c75675_uniq" btree (source)
    "database_name_target_426d2f46_uniq" btree (target)

Hardware:

I've tried increasing the server power to an 8GB memory instance and updated the .conf file with the following from PGTune:

max_connections = 10
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 209715kB
maintenance_work_mem = 512MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Despite the higher work_mem setting, it's still using a disk write for the merge which is confusing to me. Perhaps the window function is causing this behaviour?

Please edit your question and add the create table statements for the tables in question and the execution plan generated using explain (analyze, verbose). Formatted text please, no screen shots — user330315
– user330315, Commented May 12, 2017 at 19:52
I don't think I can, sorry - but I understand the request. Happy to take your advice and delete if it's not possible to answer without those. (The database was created by the ORM and all the data inserted that way also so I don't have that available. I'd be dropping into raw SQL just for this query.) — Phil Sheard
– Phil Sheard, Commented May 12, 2017 at 19:58
is target indexed?.. what is the cardinality of target column data? — Vao Tsun
– Vao Tsun, Commented May 12, 2017 at 20:25
you can run '\d <tablename>' in psql to look for table structure. And aslso please provide result of 'explain analyze select ....' — Roman Tkachuk
– Roman Tkachuk, Commented May 13, 2017 at 3:55
Thanks - have now added further detail. Table structure, result of EXPLAIN ANALYZE, and a test on more powerful hardware to test RAM increase. Any advice will be gratefully received :) — Phil Sheard
– Phil Sheard, Commented May 15, 2017 at 9:03

Laurenz Albe · Accepted Answer · 2017-05-13 08:17:15Z

4

Your query is already optimal. There is no way to avoid scanning the whole table to get the information you need, and a sequential scan is the best way to do that.

Make sure that work_mem is big enough that the aggregationcan be done in memory – you can set log_temp_files to monitor if temporary files are used (which makes things much slower).

answered May 13, 2017 at 8:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Phil Sheard Over a year ago

Thanks Laurenz, that's helpful. I have tried increasing the RAM on the server instance as you're right, it was writing the merge to disk. Unfortunately it's not taking advantage of the increased work_mem setting despite being almost twice what's required. If you've got any insight on that, it would be appreciated.

Laurenz Albe Over a year ago

Yes, increase work_mem even more. The amount of memory required is way larger then the size of the temporary file.

Phil Sheard Over a year ago

Thanks Laurenz, I've accepted your answer as correct as it helped me understand that the query was optimised as much as possible and either needed a more powerful (= more memory) server or for me to re-engineer the query. I can't justify the scale of server needed just now so I'm removing the lookup and pushing the work onto the client instead.

Rabeez Riaz Over a year ago

I stumbled on this answer after hitting my head against the wall for days and telling myself "Your query must not be optimized, make it faster". It turns out the work_mem was at 4MB and I was hitting the temp files

Collectives™ on Stack Overflow

Speed up slow Postgres query with window functions

EDIT: Further info and tests on higher memory

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT: Further info and tests on higher memory

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related