Timeline for Speeding up a GROUP BY, HAVING COUNT query
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 23, 2017 at 12:40 | history | edited | CommunityBot |
replaced http://stackoverflow.com/ with https://stackoverflow.com/
|
|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Mar 15, 2016 at 2:19 | comment | added | Erwin Brandstetter |
@SGr: I would advise against using SET LOCAL enable_seqscan = off in production if at all possible. Tune your DB configuration and cost settings and run VACUUM ANALYZE. Also, 10 ms seems expensive for my first query, I see around 1 ms in my tests. Try REINDEX SYSTEM and see if it's faster then. (re-posted to fix format)
|
|
| Mar 4, 2016 at 1:20 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
add more
|
| Mar 3, 2016 at 18:55 | comment | added | SGr |
WOW. This is an amazing answer. The contents of the table change drastically and continuously, but in my tests your 'quick estimates' query is good enough for my needs and takes about 10ms. If it ends up not being accurate enough I'll do BEGIN; SET LOCAL enable_seqscan TO off; 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; END; which is about 10 times slower, but still another 10 times faster than the basic query.
|
|
| Mar 3, 2016 at 18:52 | vote | accept | SGr | ||
| Mar 3, 2016 at 3:49 | history | answered | Erwin Brandstetter | CC BY-SA 3.0 |