Re: SQL compatibility reminder: MySQL vs PostgreSQL

> As far as I can tell, we already do index skip scans:

This feature is great but I was thinking about something else, like SELECT
DISTINCT, which currently does a seq scan, even if x is indexed.

Here is an example. In both cases it could use the index to skip all
non-interesting rows, pulling only 69 rows from the heap instead of 120K.

EXPLAIN ANALYZE SELECT DISTINCT vente, type_id FROM annonces;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=15270.98..15271.82 rows=84 width=3) (actual
time=113.277..113.288 rows=69 loops=1)
-> Seq Scan on annonces (cost=0.00..14682.32 rows=117732 width=3)
(actual time=0.005..76.069 rows=119655 loops=1)

EXPLAIN ANALYZE SELECT DISTINCT ON( vente, type_id ) * FROM annonces;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.00..34926.90 rows=84 width=1076) (actual
time=0.019..107.318 rows=69 loops=1)
-> Index Scan using annonces_type on annonces (cost=0.00..34338.24
rows=117732 width=1076) (actual time=0.017..52.982 rows=119655 loops=1)