Run Code  | API  | Code Wall  | Misc  | Feedback  | Login  | Theme  | Privacy  | Patreon 

distinct on vs row_number

Language: Layout:
absolute service time: 1,45 sec 
edit mode |  history  | discussion
   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