LP uses GIST indices which are slow to query compared to GIN indices
Bug #306201 reported by
Stuart Bishop
This bug affects 1 person
| Affects | Status | Importance | Assigned to | Milestone | |
|---|---|---|---|---|---|
| Launchpad itself |
Fix Released
|
High
|
Stuart Bishop | ||
| PostgreSQL |
Fix Released
|
Undecided
|
Unassigned | ||
Bug Description
If we convert our GIST indexes used by tsearch2 to GIN, we occasionally have queries abort with the following error message:
Query requires full scan, GIN doesn't support it
As the querys are generated from user input we can't stop this entirely. For example, searching for '-foo' will almost certainly trigger the error.
Dropping the index allows the queries to work slowly. Reverting to the GIST index is currently the workaround. Our application should see a considerable performance benefit if we could use GIN indexes.
Related branches
lp:~stub/launchpad/db-cleanups
- Stuart Bishop (community): Approve (db)
- Robert Collins: Pending (db) requested
- Launchpad code reviewers: Pending requested
-
Diff: 53 lines (+44/-0)2 files modifieddatabase/schema/patch-2209-21-2.sql (+24/-0)
database/schema/patch-2209-21-3.sql (+20/-0)
- Stuart Bishop (community): Approve (db)
-
Diff: 470 lines (+213/-195)5 files modifieddatabase/sampledata/current-dev.sql (+95/-95)
database/sampledata/current.sql (+95/-95)
database/schema/Makefile (+2/-2)
database/schema/patch-2208-90-1.sql (+8/-0)
database/schema/trusted.sql (+13/-3)
Revision history for this message
| Stuart Bishop (stub) wrote | #1 |
| Changed in launchpad-foundations: | |
| importance: | Undecided → Medium |
| status: | New → Triaged |
Revision history for this message
| Stuart Bishop (stub) wrote | #2 |
Revision history for this message
| Robert Collins (lifeless) wrote | #3 |
| Changed in launchpad: | |
| importance: | Medium → High |
| Changed in postgresql: | |
| status: | New → Fix Released |
| tags: | added: search |
| summary: |
- GIN indexes don't support full scans, causing queries to abort + LP uses GIST indices which are slow to querty |
| summary: |
- LP uses GIST indices which are slow to querty + LP uses GIST indices which are slow to query |
| tags: | added: pg9 |
| summary: |
- LP uses GIST indices which are slow to query + LP uses GIST indices which are slow to query compared to GIN indices |
Revision history for this message
| Launchpad QA Bot (lpqabot) wrote | #4 |
| Changed in launchpad: | |
| assignee: | nobody → Stuart Bishop (stub) |
| tags: | added: qa-needstesting |
| Changed in launchpad: | |
| status: | Triaged → In Progress |
| tags: |
added: qa-ok removed: qa-needstesting |
Revision history for this message
| Launchpad QA Bot (lpqabot) wrote | #5 |
| tags: |
added: qa-needstesting removed: qa-ok |
| Changed in launchpad: | |
| status: | In Progress → Fix Committed |
| tags: |
added: qa-ok removed: qa-needstesting |
| Changed in launchpad: | |
| status: | Fix Committed → Fix Released |
To post a comment you must log in.
Launchpad can switch immediately to GIN indexes for a big speed improvement if this is fixed in PostgreSQL.
Launchpad ideally should detect if a query requires a full scan and abort the search gracefully. This would also be a work around to the edge case GIN doesn't support, and allow us to switch index types for a big speed improvement. This approach is discussed in Bug 119780.