CODEWITHSUNDEEP
sqlpostgresqlindexing
Tony Wolff
Tony Wolff

Reputation: 742

Postgres query on very large table with indexes still very slow

The following query is running very slowly despite mytable being indexed on recdate (mytable has > 60 M records) query takes almost 7 minutes.

select to_char(recdate, 'yyyy'), count(*)
from mytable
group by to_char(recdate, 'yyyy')

Upvotes: 1

Views: 442

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175666

You didn't provide execution plan, but I guess function-based index would cover

CREATE INDEX mytable_idx ON mytable( to_char(recdate,'yyyy'));

query:

select to_char(recdate,'yyyy'),count(*) 
from mytable 
group by to_char(recdate,'yyyy')

Upvotes: 2

Related Questions