0

I'm having a significant performance difference between two PostgreSQL queries that I'm trying to understand and optimize. I have a table with around 2TB of data, and both queries use the sequential scan. The first query executes quickly, while the second one takes more than 2 hours to complete. Here are the queries and some details:

1st Query

WITH cte_mytable AS (
   SELECT *, coalesce(array_length(tags, 1), 0) as size FROM my_table limit 100
)
SELECT
  user_id, ...some other columns
FROM
  cte_mytable
WHERE
  cte_mytable.user_id=user_id
ORDER BY
  size desc;

Explain log:

|QUERY PLAN                                                                                         |
|---------------------------------------------------------------------------------------------------|
|Sort  (cost=13.02..13.27 rows=100 width=593)                                                       |
|  Sort Key: cte_my_table.size DESC                                                                     |
|  ->  Subquery Scan on cte_my_table  (cost=0.00..9.70 rows=100 width=593)                              |
|        Filter: ((cte_my_table.user_id)::text IS NOT NULL)                                             |
|        ->  Limit  (cost=0.00..8.70 rows=100 width=593)                                            |
|              ->  Seq Scan on my_table  (cost=0.00..197608426.93 rows=2272637194 width=593)|

2nd Query

SELECT *
FROM my_table
ORDER BY array_length(tags, 1) desc
LIMIT 100;

Explain log

|QUERY PLAN                                                                                                 |
|-----------------------------------------------------------------------------------------------------------|
|Limit  (cost=217229180.49..217229192.16 rows=100 width=593)                                                |
|  ->  Gather Merge  (cost=217229180.49..438195445.87 rows=1893864328 width=593)                            |
|        Workers Planned: 2                                                                                 |
|        ->  Sort  (cost=217228180.47..219595510.88 rows=946932164 width=593)                               |
|              Sort Key: (array_length(tags, 1)) DESC                                                       |
|              ->  Parallel Seq Scan on my_table  (cost=0.00..181037114.05 rows=946932164 width=593)|

Version

PostgreSQL 14.6

Can someone help me understand why the second query is so slow and the first query is faster?

6
  • first query returns some random data (you are asking db to take 100 arbitrary rows from heap), second one - something reliable Sep 4, 2023 at 14:02
  • @AndreyB.Panfilov Could you drop an answer and explain a bit more?
    – sujeet
    Sep 4, 2023 at 14:07
  • That is bit unclear what has confused you. If you consider subquery factoring as a convenient approach of writing complex queries, this particular case does not suit your needs because due to limit clause in CTE PostgreSQL (and any other db engine too) is unable to unnest it into main query, so, limit always gets applied before filtering and you do see some random data Sep 4, 2023 at 14:45
  • @AndreyB.Panfilov - maybe you should check out the MATERIALIZED keyword with CTEs in PostgreSQL - the OP should look at it also!
    – Vérace
    Sep 4, 2023 at 14:49
  • 1
    The two queries are calculating entirely different things, and the first query is pretty meaningless. The first query selects the first best 100 rows from the large table, the second has to find the 100 rows with the most elements in tags. Sep 5, 2023 at 2:38

1 Answer 1

2

This is my take ..

The first query is faster because it applies LIMIT before doing the ORDER BY. The Common Table Expression (CTE) cte_mytable first limits the number of rows to 100. Then, the outer query applies the ORDER BY clause to these 100 rows. As a result, the sort operation is performed on a smaller dataset, which is faster.

the second query does a sequential scan on the entire table, computes the array_length(tags, 1) for each row, sorts the entire table based on this computation, and then applies the LIMIT. This process is much slower because the ORDER BY operation is performed on the entire dataset before limiting the result set.

If you need to perform the ORDER BY operation on the entire table and then limit the results, consider creating an index on the computed array_length(tags, 1). This can significantly speed up the sorting operation, but be aware that maintaining the index can also introduce overhead, especially if my_table is frequently updated.

1
  • Your answer is perfect, if could just add the Laurenz Albe's or Andrey B.'s comment in your answer.
    – sujeet
    Sep 5, 2023 at 3:25

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.