CODEWITHSUNDEEP
postgresql
Jimesh Gajera
Jimesh Gajera

Reputation: 612

Select query became very very very slow in postgresql

I have one table which contains "133,072,194" records and I am trying to execute 

SELECT COUNT(test) 
FROM mytable 
WHERE test = false

but it is taking Execution time: 128320.712 ms

I already have indexing on test column. Could you please let me know, what I can optimize or change, so my query became faster?

Because of this, my other select query is also not working.

Upvotes: 0

Views: 803

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

If there are many rows where test is FALSE, you won't be able to get an exact result faster than with a sequential scan, which is slow for big tables.

If you have only few rows that satisfy the condition, you should create a partial index:

CREATE INDEX mytable_notest_ind ON mytable(id) WHERE NOT test;

(assuming that id is the primary key) and keep mytable autovacuumed often enough that you get an index only scan.

But usually exact results for queries like this are not required.

You could calculate an estimated count from the table statistics with a query like this:

SELECT t.reltuples
       * (1 - t.nullfrac)
       * mcv.freq AS count_false
FROM pg_stats AS s
   CROSS JOIN LATERAL unnest(s.most_common_vals::text::boolean[],
                             s.most_common_freqs) AS mcv(val, freq)
   JOIN pg_class AS t
      ON s.tablename = t.relname
         AND s.schemaname = t.relnamespace::regnamespace::text
WHERE s.tablename = 'mytable'
  AND s.attname = 'test'
  AND mcv.val = FALSE;

That would be very fast.

See my blog post for more considerations about the speed of SELECT count(*).

Upvotes: 1

Related Questions