I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question Although my query is using indices, how can I make the query faster?

mydb=> explain analyze select count(scores), sum(scores) from mytable 
       where classes in ('1st','2nd','3rd') and school_year in ('2018');

 Aggregate  (cost=1150864.84..1150864.85 rows=1 width=40) (actual time=185454.773..185454.773 rows=1 loops=1)
   ->  Bitmap Heap Scan on mytable  (cost=16998.63..1146247.22 rows=923525 width=6) (actual time=934.546..184704.246 rows=967663 loops=1)
         Recheck Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
         Filter: (school_year = 2018)
         Heap Blocks: exact=448386
         ->  Bitmap Index Scan on mytable_classes_idx  (cost=0.00..16767.75 rows=923525 width=0) (actual time=736.690..736.690 rows=967663 loops=1)
               Index Cond: ((classes)::text = ANY ('{1st,2nd,3rd}'::text[]))
 Planning time: 1.252 ms
 Execution time: 185463.153 ms
(9 rows)

Additional Details

 PostgreSQL 10.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
(1 row)

One odd thing is that the filter Filter: (school_year = 2018) doesn't remove any rows. So either the entire table has school_year = 2018, or all the rows which meet the criteria classes in ('1st','2nd','3rd') have school_year = 2018. Either way, it seems a bit odd. Is this how your real problem is, or is it an artefact of creating a smaller demonstration problem?

The slowness probably comes from the IO needed to read a large amount of the table. Doing the EXPLAIN (ANALYZE, BUFFERS), especially with "track_io_timing" turned on, would be especially helpful to figure that out.

If so, creating an index that includes all of the columns, like:

create index on mytable (classes, school_year, score);

should make this much faster. It will allow an index-only scan, instead of an scan which still needs to visit the table for each row. But for this to be effective, the table must be kept well-vacuumed. The default settings of autovac will probably not be enough.

  • Hi - I only had two indices on the table mytable(classes) and mytable(school_year). I will try with a combined index as you suggested. Could you please let me know what you mean by well-vacuumed? Do I need to do something in order to keep the table vaccumed? There are no updates/inserts being made to this table. It will just serve as a point in time db – Anthony Aug 16 at 19:57
  • Since the table is static, then just vacuum it manually once, and that should take care of it. If you do bulk inserts into it (like at the end of each school year) you can just do a manual vacuum after each bulk insert. – jjanes Aug 16 at 20:28
  • Sorry, this might sound dumb...but how can I vaccum the table? – Anthony Aug 17 at 1:56
  • Issue vacuum mytable; from any tool which lets you issue SQL directly to the database. – jjanes Aug 17 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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