1

I need to extract two fields from a table. Here's the query with just one of the fields:

set schema 'data';
    explain (analyze, verbose)
     select 
        count(example9),
        example9,
        example5
    from table
    group by  table.example9, table.example5
    order by count(example9) desc

Here's the query plan:

"Sort  (cost=151128.71..151149.41 rows=8280 width=79) (actual time=1602.128..1602.164 rows=1852 loops=1)"
"  Output: (count(example9)), example9, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 316kB"
"  ->  HashAggregate  (cost=150507.08..150589.88 rows=8280 width=79) (actual time=1601.299..1601.705 rows=1852 loops=1)"
"        Output: count(example9), example9, example5"
"        Group Key: table.example9, table.example5"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=79) (actual time=0.013..633.542 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.108 ms"
"Execution time: 1602.380 ms"

Here's the query with the second field I want:

set schema 'data';
    explain (analyze, verbose)
     select 
        count(example9),
        example9,
        example6
    from table
    group by  table.example9, table.example6
    order by count(example9) desc

and the corresponding query plan:

"Sort  (cost=152197.99..152249.74 rows=20700 width=85) (actual time=1618.241..1618.265 rows=1794 loops=1)"
"  Output: (count(example9)), example9, example6"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 313kB"
"  ->  HashAggregate  (cost=150507.08..150714.08 rows=20700 width=85) (actual time=1617.381..1617.799 rows=1794 loops=1)"
"        Output: count(example9), example9, example6"
"        Group Key: table.example9, table.example6"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=85) (actual time=0.005..635.558 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.067 ms"
"Execution time: 1618.559 ms"

Now (this is the problem), here are the two fields combined together in the same query:

set schema 'data';
    explain (analyze, verbose)
     select 
        count(example9),
        example9,
        example6,
        example5
    from table
    group by  table.example9, table.example6, table.example5
    order by count(example9) desc

and here is the query plan for it:

"Sort  (cost=794824.44..795341.94 rows=207000 width=92) (actual time=23189.695..23189.725 rows=1872 loops=1)"
"  Output: (count(example9)), example9, example6, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 350kB"
"  ->  GroupAggregate  (cost=728162.97..765931.60 rows=207000 width=92) (actual time=19125.027..23189.203 rows=1872 loops=1)"
"        Output: count(example9), example9, example6, example5"
"        Group Key: table.example9, table.example6, table.example5"
"        ->  Sort  (cost=728162.97..735302.70 rows=2855890 width=92) (actual time=19110.027..22635.209 rows=2807146 loops=1)"
"              Output: example9, example6, example5"
"              Sort Key: table.example9, table.example6, table.example5"
"              Sort Method: external merge  Disk: 286688kB"
"              ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=92) (actual time=0.009..962.086 rows=2807146 loops=1)"
"                    Output: example9, example6, example5"
"Planning time: 0.079 ms"
"Execution time: 23556.835 ms"

Why does the query suddenly become so much more loaded when I tack on that extra group-by?

EDIT: example9, example6, and example5 are all character varying

  • Please mark the answer as correct - that may help others with the same problem :-) – Vérace Apr 18 '16 at 22:33
2

The reason why the third statement is slow is it has to group 2.8 million rows which cannot be done in memory any longer. So the sorting is done on disk which obviously is a lot slower:

You can see that in this line:

Sort Method: external merge  Disk: 286688kB"

The number of rows and the time it took can be seen in this line::

Sort  (...) (actual time=19110.027..22635.209 rows=2807146 loops=1)

You can also see that the first and second statements did the grouping using a hash function and both only had to process less than 2000 rows.

HashAggregate  (cost=...) (actual time=1617.381..1617.799 rows=1794 loops=1)

The memory that Postgres is allowed to use for sorting is controlled through the configuration parameter work_mem. If you increase that until the sorting can be done in memory, the performance should get better.

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.