Duplicate Conditions in PostgreSQL Query



  • Can using duplicate conditions in PostgreSQL cause any problems?

    For example, I have a purchases table with a customer id, and my framework in some cases duplicates a lookup condition, like the example below.

    SELECT p.*
    FROM purchases p
    WHERE p.customer_id = 123 
    AND p.customer_id = 123;
    

    OR

    SELECT p.*
    FROM purchases p
    WHERE p.customer_id = 123 
    AND p.salesman = 456 
    AND p.departament = 789 
    AND p.customer_id = 123;
    


  • Yes, that can cause bad query estimates. If PostgreSQL thinks that WHERE p.customer_id = 123 will return 10% of the rows from the table, it will think that WHERE p.customer_id = 123 AND p.customer_id = 123 will only return 1% of the table, because it does not realize that the conditions are actually the same and treats them as statistically independent.

    Bad estimates can lead to bad plan choices and bad performance.

    Perhaps that does not matter so much in your case: PostgreSQL puts a lower limit of 1 on each row count estimate, so if your de-duplicated query is already estimated to return very few rows, not much harm will be done.

    Try using EXPLAIN!




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2