1

PostgreSQL 9.4

Consider the following pretty simlpe query:

EXPLAIN ANALYZE SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY date DESC NULLS LAST) rn
    FROM tbl) sub WHERE rn = 2123231

The analyzed plan is

                HERE
------------------------------------------------->
                                                 |
                                                 |
Subquery Scan on sub  (cost=0.42..23380.71 rows=1216 width=40) (actual time=269.947..269.947 rows=0 loops=1)
  Filter: (sub.rn = 2123231)
  Rows Removed by Filter: 243147
  ->  WindowAgg  (cost=0.42..20340.47 rows=243219 width=32) (actual time=7.361..248.437 rows=243147 loops=1)
        ->  Index Scan using idx on tbl  (cost=0.42..14260.00 rows=243219 width=32) (actual time=0.014..66.079 rows=243147 loops=1)

My question is how the total rows 1216 is computed. Is there any strictly defined rule for that?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.