0

I have a table that I am trying to query. Here is the create statement for the table

   CREATE TABLE IF NOT EXISTS public.TableName
   (
       i integer NOT NULL,
       t integer NOT NULL,
       v real NOT NULL,
       h smallint NOT NULL,
       CONSTRAINT TableName_key_i_t_h UNIQUE (i, t, h)
   )

The indexing is done using the unique constraint. My query is as below. It appears not to be using the indexing. What am I doing wrong?

aggregate_minpower_query = f"""select i, min(p) from {tileTableName} 
            where  
            i = ANY (%(iList)s) and
            t = ANY (%(tList)s) and
            h < (%(hval)s) 
            group by i"""

Should I be using IN?

aggregate_minpower_query = f"""select i, min(p) from {tileTableName} 
            where  
            i IN %(iList)s and
            t IN %(tList)s and
            h < (%(hval)s) 
            group by i"""

1

1 Answer 1

2

Put the column for which the condition is most selective first in the constraint definition. Experiment with enable_seqscan = off to see if PostgreSQL is right if it thinks that using an index won't make the query faster. – Laurenz Albe

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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