I have below query to fetch list of tickets.

EXPLAIN select * from ticket_type
                where ticket_type.event_id='89898'
                and ticket_type.active=true
                and (ticket_type.is_unlimited = true OR ticket_type.number_of_sold_tickets < ticket_type.number_of_tickets)
                order by ticket_type.ticket_type_order

I have created below indexes but not working.

1. Index on (ticket_type_order,event_id,is_unlimited,active)
2. Index on (ticket_type_order,event_id,active,number_of_sold_tickets,number_of_tickets).

CodePudding user response：

The perfect index for this query would be

CREATE INDEX ON ticket_type (event_id, ticket_type_order)
   WHERE active AND (is_unlimited OR number_of_sold_tickets < number_of_tickets);

Of course, a partial index like that might only be useful for this specific query.

If the WHERE conditions from the index definition are not very selective, or a somewhat slower execution is also acceptable, you can omit parts of or the whole WHERE clause. That makes the index more widely useful.

CodePudding user response：

What is the size of the table and usual query result? The server is usually smart enough and disables indexes, if it expects to return more than the half of the table.

Index makes no sense, if the result is rather small. If the server has - let say - 1000 records after several filtration steps, the server stops using indexes. It is cheaper the finish the query using CPU, then loading an index from HDD. As result, indexes are never applied to small tables.

Order by is applied at the very end of the query processing. The first field in the index should be one of the fields from the where filter.

Boolean fields are seldom useful in the index. It has only two possible values. Index should be created for fields with a lot of different values.

Avoid or filtering. It is easy in your case. Put a very big number into number_of_tickets, if the tickets are unlimited.

The better index in your case would be just event_id. If the database server supports functional indexes, then you can try to add number_of_tickets - number_of_sold_tickets. Rewrite the statement as where number_of_tickets - number_of_sold_tickets > 0

UPDATE: Postgresql calls it "Index on Expression":

https://www.postgresql.org/docs/current/indexes-expressional.html