3

We had an issue in where a query would hang indefinitely with the inclusion of certain IDs in the last WHERE IN statement here:

SELECT 1
FROM items
WHERE id NOT IN (
                 SELECT items_id
                 FROM "items_attribute"
                 WHERE "items_attribute"."attribute_id" IN
                       (88376, 88354, 88380, 74984, 12983, 1205, 10931, 78061)
);

Those IDs that caused the hanging did not seem to present with any data anomaly. Do you see any issues with this query that would cause recursion or any potential issues? We seemed to resolve the issue by making the subselect DISTINCT. The select in question returns 164760 rows. There are no views here, only small indexed tables.

Explain:

Seq Scan on items (cost=2594.08..3816558214.87 rows=164034 width=0)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=2594.08..25511.56 rows=139699 width=4)
           ->  Bitmap Heap Scan on items_attribute  (cost=2594.08..24267.06 rows=139699 width=4)
                 Recheck Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))
                 ->  Bitmap Index Scan on items_attribute_attribute_id_index  (cost=0.00..2559.16 rows=139699 width=0)
                       Index Cond: (attribute_id = ANY ('{88376,88354,88380,74984,12983,1205,10931,78061}'::integer[]))

Thank you so much for taking a look!!

closed as unclear what you're asking by Evan Carroll, RDFozz, hot2use, Erik Darling, mustaccio Mar 19 '18 at 12:45

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Is that the plan for the slow one or the fast one? Whichever it is, please post the plan for the other one, too. For the fast one, make it an explain (analyze, buffers). – jjanes May 1 '17 at 22:17
  • 1
    Rewrite your query using SELECT 1 FROM items WHERE NOT EXISTS (SELECT 1 FROM items_attribute WHERE ... ). EXISTS is faster than IN. – pietrop May 2 '17 at 9:20
  • Is there concurrent access? – eckes Mar 16 '18 at 21:25
  • Whatever the problem is, there isn't enough information to solve it. Though I would try pietrops suggestion if for no other reason because it handles nulls and will be planned like an anti-join. – Evan Carroll Mar 16 '18 at 22:17
-3

Try dropping and recreating any indexes on items.id or items_attribute.attribute_id.

  • Thanks a lot for replying. Both those fields are individually btree indexed. Do you think the sequential scan could be doing it? i.e should I look into how to make postgres use an index? – Jordan May 1 '17 at 16:19
  • I have had queries fail (not hang) due to a corrupt index. I see you've got at least one called items_attribute_attribute_id_index. – CalZ May 1 '17 at 16:36
  • 1
    I just want to chime in that I'm facing a problem that I think is similar here. A query for dates between 1st and 10th works fine, but for dates between 1st and 11th fails. I can do a different query for the 11th and such without a problem. I finally noticed that the query plan for the hang is using an index scan, so I'm going to try rebuilding the index to see if it might be corrupt – deinspanjer Nov 15 '17 at 12:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.