Revisions to Speeding up a GROUP BY, HAVING COUNT query

added [postgresql-performance] to 571 questions - Shog9 (Id=1924)

Link

edited Jan 7, 2020 at 22:25

Community Bot

1

Tweeted twitter.com/StackDBAs/status/705774742045900800

occurred Mar 4, 2016 at 15:19

tags, minor edits

Source Link

edited Mar 3, 2016 at 3:51

Erwin Brandstetter

182.2k
28
457
620

I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on groupingsFrameHash"groupingsFrameHash". I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on groupingsFrameHash. I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on "groupingsFrameHash". I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

postgresql performance index performance count

added 16 characters in body

Source Link

edited Mar 2, 2016 at 19:39

SGr

205
1
5

I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on groupingsFrameHash. I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

I'm trying to speed up this query:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on groupingsFrameHash. I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

I'm trying to speed up this query in Postgres 9.4:

SELECT "groupingsFrameHash", COUNT(*) AS nb
FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"
GROUP BY "groupingsFrameHash"
HAVING COUNT(*) > 1
ORDER BY nb DESC LIMIT 10

I have an index on groupingsFrameHash. I don't need exact results, a fuzzy approximation is good enough.

Here is the query plan:

Limit  (cost=17207.03..17207.05 rows=10 width=25) (actual time=740.056..740.058 rows=10 loops=1)
  ->  Sort  (cost=17207.03..17318.19 rows=44463 width=25) (actual time=740.054..740.055 rows=10 loops=1)
        Sort Key: (count(*))
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=14725.95..16246.20 rows=44463 width=25) (actual time=615.109..734.740 rows=25977 loops=1)
              Group Key: "groupingsFrameHash"
              Filter: (count(*) > 1)
              Rows Removed by Filter: 24259
              ->  Sort  (cost=14725.95..14967.07 rows=96446 width=25) (actual time=615.093..705.507 rows=96026 loops=1)
                    Sort Key: "groupingsFrameHash"
                    Sort Method: external merge  Disk: 3280kB
                    ->  Seq Scan on "zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876"  (cost=0.00..4431.46 rows=96446 width=25) (actual time=0.007..33.813 rows=96026 loops=1)
Planning time: 0.080 ms
Execution time: 740.877 ms

I don't understand why it needs to do a Seq Scan.

Source Link

asked Mar 2, 2016 at 19:21

SGr

205
1
5

Loading

Stack Exchange Network

Return to Question