postgresql - to compound index or not to compound index (postgres) -

postgresql - to compound index or not to compound index (postgres) -

i have page of listings loading slowly, want check have right indexes on it.

the table has 5 1000000 rows in it. each listing belongs channel_id. webpage displays listings 1 channel, paginated, first 'filter' 'channel_id'. i'll omit offset , limit brevity in these examples.

select * listings channel_id = 5;

a listing has status column , default page opens on 'active' listings.

select * listings channel_id = 5 , status = 'active';

so created compound index on (channel_id, status).

but want filter , have sortable table columns in ui, on bunch of other attributes (price, quantity, type, sku, title, has_errors, has_warnings, created_at, updated_at ... etc)

i started compound index of of attributes together, doesn't create much sense since sort on 1 column @ time ui.

the postgres docs say: "if 1 of types of query much less mutual others, you'd settle creating 2 indexes best match mutual types"

so sounds should creating several individual indexes instead of 1 giant compound index. question is: if know i'm dealing 1 channel @ time, improve create index on 'created_at', or still create compound indexes each attribute channel_id in every index eg: (channel_id, created_at) , (channel_id, title) , forth?

i'll omit offset , limit brevity in these examples.

but omitted order by relevant indexing.

but want filter , have sortable table columns in ui, on bunch of other attributes

we can give particular advice particular query.

in context, postgresql doc's quote applicable: cannot create indexes possible combinations, chose mutual ones , take care of them.

so i'd suggest 2 mutual queries there where , order by clauses , add together them question. can have @ , give advice.

alternatively, preferably, start learning indexing here.

finally, please aware pagination queries special field of optimization , offset performance threat improve avoided: see nooffset (slides there featuring postgresql benchmarks).

postgresql indexing