Timeline for Improving performance of count where only result > 0 matters
Current License: CC BY-SA 4.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 23, 2019 at 6:56 | comment | added | Erwin Brandstetter |
@isADon: Yes, like I said, without index, your original expression is faster. A trigram index is the weapon of choice for the given unanchored patterns. See: dba.stackexchange.com/a/10696/3684 Implementation details depend on additional information: cardinalities, value frequencies, row sizes, clarifications about your schema. And you may need ANALYZE between index creation and the query. See: dba.stackexchange.com/a/99324/3684
|
|
| Apr 22, 2019 at 8:58 | vote | accept | isADon | ||
| Apr 22, 2019 at 8:58 | comment | added | isADon |
Thanks for this detailed answer! SELECT EXISTS made a big improvement. However I did not see any improvement using p.tag ~* 'verified' with the index on the column as described. The query runs about 20% faster with the like syntax
|
|
| Apr 21, 2019 at 22:18 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 1 character in body
|
| Apr 21, 2019 at 22:11 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
add index details
|
| Apr 21, 2019 at 21:51 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 636 characters in body
|
| Apr 21, 2019 at 21:46 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
added 636 characters in body
|
| Apr 21, 2019 at 21:38 | history | answered | Erwin Brandstetter | CC BY-SA 4.0 |