On Wed, Oct 23, 2013 at 10:58 PM, Tom Lane wrote:
> Sameer Kumar writes:
> > I am not sure why but my PostgreSQL does not seem to be using indexes for
> > ORDER BY clause or PARTITION BY CLAUSE which I use with windowing
> function.
>
> When the entire contents of the table have to be read, a seqscan-and-sort
> will frequently be estimated as cheaper than an indexscan. If you think
> this is not true on your hardware, you might need to adjust
> random_page_cost.
>
> regards, tom lane
>
My mistake. I had understood the issue wrongly.
Actually when I use functions like max to find the maximum value grouped by
another column I get a better performance when I try to do the same
operation using max() over().
Take a look at below plan:
edb=# \x
Expanded display is on.
edb=# \dS= student_score;
Table "enterprisedb.student_score"
Column| Type | Modifiers
--+-+---
id | integer | not null
student_name | character varying(1000) |
score| integer |
course | character varying(100) |
Indexes:
"student_score_pkey" PRIMARY KEY, btree (id)
"idx_course" btree (course)
"idx_score" btree (score)
edb=# select count(*) from student_score ;
-[ RECORD 1 ]-
count | 122880
edb=# explain analyze select max(score) from student_score group by course;
-[ RECORD 1
]-
QUERY PLAN | HashAggregate (cost=3198.20..3198.26 rows=6 width=9) (actual
time=110.792..110.793 rows=6 loops=1)
-[ RECORD 2
]-
QUERY PLAN | -> Seq Scan on student_score (cost=0.00..2583.80
rows=122880 width=9) (actual time=0.011..23.055 rows=122880 loops=1)
-[ RECORD 3
]-
QUERY PLAN | Total runtime: 110.862 ms
edb=# explain analyze select max(score) over(partition by course) from
student_score ;
-[ RECORD 1
]
QUERY PLAN | WindowAgg (cost=0.00..10324.65 rows=122880 width=9) (actual
time=36.145..224.504 rows=122880 loops=1)
-[ RECORD 2
]
QUERY PLAN | -> Index Scan using idx_course on student_score
(cost=0.00..8481.45 rows=122880 width=9) (actual time=0.037..85.283
rows=122880 loops=1)
-[ RECORD 3
]
QUERY PLAN | Total runtime: 242.949 ms
AS you can see there is a difference of twice. On similar lines, when I
have to find students who "topped" (had highest score) per course, I will
fire something like below:
edb=# explain analyze select student_name from student_score where
(course,score)in (select course,max(score) from student_score group by
course);
-[ RECORD 1
]---
QUERY PLAN | Hash Semi Join (cost=3198.41..6516.76 rows=7300 width=43)
(actual time=113.727..181.045 rows=555 loops=1)
-[ RECORD 2
]---
QUERY PLAN | Hash Cond: (((enterprisedb.student_score.course)::text =
(enterprisedb.student_score.course)::text) AND
(enterprisedb.student_score.score =
(max(enterprisedb.student_score.score
-[ RECORD 3
]---
QUERY PLAN | -> Seq Scan on student_score (cost=0.00..2583.80
rows=122880 width=52) (actual time=0.009..22.702 rows=122880 loops=1)
-[ RECORD 4
]---
QUERY PLAN | -> Hash (cost=3198.32..3198.32 rows=6 width=9) (actual
time=111.521..111.521 rows=6 loops=1)
-[ RECORD 5
]---
QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 1kB
-[ RECORD 6
]--
