How to search date column via index, not via filter?

Question

three is date column, oscar_alpha is integer.

I have this result from Postgres explain. On 16 row I want to prevent acces to table to find in filter condition

Filter: ((three >= '15.04.2010 00:00'::date) AND (three <= '15.04.2016 00:00'::date) AND (oscar_alpha = 341)).

And I want to check this condition via indexes. I created indexes:

create index index1 on table_name (three , oscar_alpha);
create index index2 on table_name (three);
create index index3 on table_name (oscar_alpha);

But they are not using.

Yes, some data anonymised.

Quassnoi · Accepted Answer · 2016-04-18 15:18:31Z

1

You need to create a composite index on (oscar_alpha, three), in that order.

An index is sorted in lexicographic order, first on the value of the first column, then, that being equal, on the second etc, like a phonebook: surnames first, then names.

It's easy to get all Smiths from John to Mary from a phonebook, but not that easy to get all Johns from Jones to Smith, because the entries are ordered on surname first.

Same with your query: if you order on oscar_alpha first, all values with the same oscar_alpha go in three order, and it's possible to apply a range condition to them.

answered Apr 18, 2016 at 15:18

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Usm Over a year ago

I added a composite index on (oscar_alpha, three), but it`s not workiing

Quassnoi Over a year ago

@Usm: oh I see. You want a composite index on quebec_seven, oscar_alpha, three.

Quassnoi Over a year ago

@Usm: unfortunately, there's not much I can do without seeing the data and the query.

Collectives™ on Stack Overflow

How to search date column via index, not via filter?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related