CartoDB/cartodb — pull request #1186 by rafatower (2014-11-17 16:44:13+00:00)
Title: 1167 guessing no seq scan

Modify the guesser sampling code in order to use index scan queries instead of seq scan.

Example of queries used for sampling, on a 580K rows table:
```
SELECT min(ogc_fid), max(ogc_fid) FROM "cdb_importer"."importer_819bcd546e7611e4a8d04e5610ba39b9"
```
and the EXPLAIN ANALYZE results for it:
```
Result (cost=0.97..0.98 rows=1 width=0) (actual time=0.040..0.040 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.42..0.49 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)
-> Index Only Scan using importer_819bcd546e7611e4a8d04e5610ba39b9_pkey on importer_819bcd546e7611e4a8d04e5610ba39b9 (cost=0.42..28976.73 rows=450989 width=4) (actual time=0.021..0.021 rows=1 loops=1)
Index Cond: (ogc_fid IS NOT NULL)
Heap Fetches: 1
InitPlan 2 (returns $1)
-> Limit (cost=0.42..0.49 rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1)
-> Index Only Scan Backward using importer_819bcd546e7611e4a8d04e5610ba39b9_pkey on importer_819bcd546e7611e4a8d04e5610ba39b9 importer_819bcd546e7611e4a8d04e5610ba39b9_1 (cost=0.42..28976.73 rows=450989 width=4) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (ogc_fid IS NOT NULL)
Heap Fetches: 1
Total runtime: 0.060 ms
```
and to actually get the sample:
```
SELECT * FROM "cdb_importer"."importer_819bcd546e7611e4a8d04e5610ba39b9" WHERE ogc_fid IN (423169,418033,140858,...)
```
```
Bitmap Heap Scan on importer_819bcd546e7611e4a8d04e5610ba39b9 (cost=1596.60..2954.52 rows=400 width=36) (actual time=3.138..3.757 rows=400 loops=1)
Recheck Cond: (ogc_fid = ANY ('{423169,418033,...}'::integer[]))
-> Bitmap Index Scan on importer_819bcd546e7611e4a8d04e5610ba39b9_pkey (cost=0.00..1596.50 rows=400 width=0) (actual time=3.084..3.084 rows=400 loops=1)
Index Cond: (ogc_fid = ANY ('{423169,418033,...}'::integer[]))
Total runtime: 4.090 ms
```

If the table_size < sample_size then there's no where clause obviously (it takes the full dataset).

Postgres can decide not to use the index if the index size is comparable to the table size, which is perfectly ok.

@javisantana can you please review?