V
V
Vitaly2019-07-02 11:19:05
PostgreSQL
Vitaly, 2019-07-02 11:19:05

What indexes to add to pgsql database table?

Table structure - 5d1b13736c3c4805033473.png
Number of records - from 50,000 to 100,000
Here are some queries for this table:
1).

select "start" from "channel_program" where "channel_id" = "channels"."id" and "end" < '2019-07-02 06:10:21' order by "start" desc limit 1

2).
select "start" from "channel_program" where "channel_id" = "channels"."id" and "start" <= '2019-07-02 06:10:21' and "end" > '2019-07-02 06:10:21' limit 1

3).
select "start" from "channel_program" where "channel_id" = "channels"."id" and "start" > '2019-07-02 06:10:21' limit 1

4).
select * from "channel_program" where "start"::date = '2019-07-02' and "channel_program"."channel_id" in (14) order by "start" asc

Answer the question

In order to leave comments, you need to log in

1 answer(s)
T
TheRonCronix, 2019-07-02
@kiukishenkaec

1. b-tree on channel_id and start seems to be successful.
2. It is worth considering the distribution of data, but this is if you understand how it affects. And so, just take yes and test your queries on a test table.
3. Do not forget to collect statistics.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question