![]() ![]() Postgres=# explain SELECT * FROM organization where 'aa'::text IN (info -> 'dept' -> 'name') Once the customer changed their query to the following, the Index started getting scanned: postgres=# SELECT * FROM org where 'aa'::text IN (info -> 'dept' -> 'name') The fix was simple, and we were able to get the customer back on their way after a rather quick adjustment to their query. Notice anything? Their query was wrapping info -> 'dept' in a function called jsonb_array_elements(), which led the query planner to think that it shouldn’t use the index. They knew they had created an index, and were curious as to why the index was not being used. Our next data point to gather was information about the index itself, and it turned out that they had created their index like so: CREATE INDEX idx_org_dept ON org ((info -> 'dept'::text) -> 'name'::text)) Postgres=# explain SELECT * FROM org where 'aa'::text IN (SELECT jsonb_array_elements(info -> 'dept') -> 'name') We got right to work to help them out, and our first stone to turn over was to have them send us their EXPLAIN ANALYZE output for the query, which yielded: They were seeing a slow performance in their development environments and were understandably worried about the impact that they’d see if they went to production with poor query performance. We recently received a request from one of our customers, concerned about a slow query on one of their JSON columns. When it comes to dealing with poor database and query performance, it’s a daunting task to venture into the dark cavern of query planning and optimization, but fear not! EXPLAIN is our friend in those dark and lonely places. Sure, there’s Slack and all manner of collaboration tools, but it’s not quite the same as walking up to someone’s cubicle and getting a second pair of eyes to look at a problem, not to mention that our co-workers might be busy trying to juggle deadlines and unruly kids in the home. With many people working from home these days because of the coronavirus pandemic, it can be a little challenging to get help from a colleague remotely. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |