Now I created a table B with same data as of A, but in ORC file format and also included sort by age while creating B.
Now when I run the query:
select count(id) from X where age=25;
The data read from B table is same as size of B (I expected predicate pushdown) and also the time taken in A is almost equal to B.
Theoretically, because of predicate pushdown in ORC, a lot of data must have been skipped thus saving a lot of time. I suspect the indexes created in orc file are not getting read. I have tried almost everything. But nothing works. Please help with this.