CODEWITHSUNDEEP
sqlpostgresql
G. Kap
G. Kap

Reputation: 11

Creating an index in my table does not lower my cost

I have this table:

accident_info
(
    accident_index varchar(20),
    first_road_class varchar(20),
    accident_severity varchar(20),
    date date,
    urban_or_rural_area varchar(20),
    weather_conditions varchar(40),
    year int,
    inscotland varchar(20)
);

And against this table, I execute the following query :

select count(accident_index)as hits, first_road_class 
from accident_info 
group by first_road_class;

without index.

I would like to create an index to lower my Aggregate Cost but the one I've made so far doesn't seem to work. This is:

create index on accident_info(accident_index, first_road_class);

First ten Rows of my table

Upvotes: 1

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

For this query:

select count(accident_index) as hits, first_road_class 
from accident_info 
group by first_road_class;

You can try an index on accident_info(first_road_class, accident_index). The order of the columns is important.

Upvotes: 1

Related Questions