Re: SQL compatibility reminder: MySQL vs PostgreSQL - Mailing list pgsql-hackers

From Pierre C
Subject Re: SQL compatibility reminder: MySQL vs PostgreSQL
Date
Msg-id op.u88thxfieorkce@localhost
Whole thread Raw
In response to Re: SQL compatibility reminder: MySQL vs PostgreSQL  (Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi>)
Responses Re: SQL compatibility reminder: MySQL vs PostgreSQL
List pgsql-hackers
> 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;
QUERYPLAN
 
-----------------------------------------------------------------------------------------------------------------------
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)


pgsql-hackers by date:

Previous
From: Marko Tiikkaja
Date:
Subject: Re: SQL compatibility reminder: MySQL vs PostgreSQL
Next
From: Marko Tiikkaja
Date:
Subject: Re: SQL compatibility reminder: MySQL vs PostgreSQL