Speed up scanning of all tables for constraints

I have been trying to speed up the scanning of the content of the database as we discussed the other day. Previously I was doing:

SELECT COUNT(*) FROM mytable WHERE x=1 AND y=2;

And it was slow, because it counted the full matched query (i.e. had to read the entire table).

So, I now try using:

SELECT x FROM mytable WHERE x=1 AND y=2 LIMIT 1;

which I expected to be much quicker... ...but it is not. Have a look at the query plan:

cdm=> explain select observed_variable from lite_2_0.observations_2011_land_0 where observed_variable=85;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on observations_2011_land_0  (cost=1395513.47..10173664.78 rows=64278825 width=4)
   Recheck Cond: (observed_variable = 85)
   ->  Bitmap Index Scan on observations_2011_land_0_observed_variable_idx  (cost=0.00..1379443.76 rows=64278825 width=0)
         Index Cond: (observed_variable = 85)
(4 rows)
cdm=> explain select observed_variable from lite_2_0.observations_2011_land_0 where observed_variable=85 limit 1;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.18 rows=1 width=4)
   ->  Seq Scan on observations_2011_land_0  (cost=0.00..11591796.68 rows=64278825 width=4)
         Filter: (observed_variable = 85)
(3 rows)

Which shows us that the INDEX is only used in the first query (without LIMIT 1).

These needs more investigation.

glamod / glamod-ingest

Speed up scanning of all tables for constraints #56