Timeline for What is the correct/fastest way to negate @> in Postgres?
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 16, 2016 at 4:59 | vote | accept | Karew | ||
| Sep 11, 2016 at 2:13 | answer | added | jjanes | timeline score: 1 | |
| Sep 9, 2016 at 15:55 | comment | added | user1822 | No index will help if you are retrieving a substantial part of the table. | |
| Sep 9, 2016 at 14:59 | comment | added | Karew | @a_horse_with_no_name Is there another type of index I can build that optimizes for this situation? Or a different WHERE clause that is more efficient for this same result? (An item is not in an array column) | |
| Sep 9, 2016 at 9:06 | comment | added | user1822 | The negated query returns 13285 rows and only 3171 rows do not match the criteria (80% of the rows of the table are returned). So Craig's assumption is right: the query simply returns too many rows in order to make an index scan efficient | |
| Sep 9, 2016 at 8:15 | history | edited | Paul White♦ | CC BY-SA 3.0 |
Removed EDIT see http://meta.stackexchange.com/a/127655
|
| Sep 9, 2016 at 1:34 | comment | added | Karew | @CraigRinger Ok, links added | |
| Sep 9, 2016 at 1:34 | history | edited | Karew | CC BY-SA 3.0 |
added 179 characters in body
|
| Sep 9, 2016 at 0:10 | comment | added | Craig Ringer |
The problem here is likely that running WHERE NOT column @> array['thing'] matches so many rows that the planner decides a different plan is better. It probably isn't selective enough. Or the selectivity estimates aren't good. If you, for testing purposes only, SET enable_bitmapscan = off and try again, is it slower or faster? What does explain show then? Show full explain (buffers, analyze) for all three queries as links to explain.depesz.com please.
|
|
| Sep 8, 2016 at 22:44 | review | First posts | |||
| Sep 9, 2016 at 8:15 | |||||
| Sep 8, 2016 at 22:41 | history | asked | Karew | CC BY-SA 3.0 |