distinct on vs row_number
|
QUERY PLAN |
1 |
Subquery Scan on tt (cost=0.42..8841.52 rows=500 width=28) (actual time=0.082..211.361 rows=1111 loops=1) |
2 |
Output: tt.hss_id, tt.hss_num, tt.hss_street, tt.hss_price, tt.hss_saled, tt.rn |
3 |
Filter: (tt.rn = 1) |
4 |
Rows Removed by Filter: 98889 |
5 |
-> WindowAgg (cost=0.42..7591.52 rows=100000 width=20) (actual time=0.076..196.917 rows=100000 loops=1) |
6 |
Output: houses.hss_id, houses.hss_num, houses.hss_street, houses.hss_price, houses.hss_saled, row_number() OVER (?) |
7 |
-> Index Scan using idx_hss_street_num_saled on public.houses (cost=0.42..5591.52 rows=100000 width=20) (actual time=0.062..126.072 rows=100000 loops=1) |
8 |
Output: houses.hss_num, houses.hss_street, houses.hss_saled, houses.hss_id, houses.hss_price |
9 |
Planning time: 0.384 ms |
10 |
Execution time: 211.573 ms |
|
QUERY PLAN |
1 |
Unique (cost=0.42..6091.52 rows=1111 width=20) (actual time=0.018..80.512 rows=1111 loops=1) |
2 |
Output: hss_id, hss_num, hss_street, hss_price, hss_saled |
3 |
-> Index Scan using idx_hss_street_num_saled on public.houses (cost=0.42..5591.52 rows=100000 width=20) (actual time=0.017..67.996 rows=100000 loops=1) |
4 |
Output: hss_id, hss_num, hss_street, hss_price, hss_saled |
5 |
Planning time: 0.139 ms |
6 |
Execution time: 80.617 ms |
|
|