0

I have a question to ask to help me understand better the indexes.Do we see any difference on this:

1
create index insertion_test_timestamp1_idx
on insertion_test (timestamp1);

create index insertion_test2_timestamp1_idx
on insertion_test (id13);

and this:

2
create index insertion_test_timestamp1_idx
on insertion_test (timestamp1,id13);

Some of my queries that i use look like this:

select * from timestampdb where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-02 00:05:00' and id13>'5',
select date_trunc('hour',timestamp1) as hour,avg(id13) from timestampdb where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-02 00:05:00' group by hour order by hour ,
select date_trunc('hour',timestamp1) as hour,max(id13) from timestampdb where timestamp1<='2020-01-01 00:05:00' group by hour order by hour desc limit 5,
select date_trunc('hour',timestamp1) as hour,max(id13) from timestamppsql where timestamp1 >='2020-01-01 00:05:00' and timestamp1<='2020-01-01 01:05:00' group by hour order by hour asc

My version is this:psql (PostgreSQL) 12.6 (Ubuntu 12.6-1.pgdg20.04+1)

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
xaroulis gekas
  • 175
  • 10
  • 2
    The first creates two indexes. The second creates one index with two columns. They are very different. – Gordon Linoff Mar 18 '21 at 18:38
  • "psql" is the default client (with its own version). The Postgres version is relevant here. (`SELECT version()`). Optimal indexing strategy will also consider the `hour` column you mention (confusing in connection with `timestamp1`). Disclose the exact table definition (`CREATE TABLE` statement showing data types and constraints), cardinalities, typical read / write activity, and more about typical queries. Instructions here: https://stackoverflow.com/tags/postgresql-performance/info – Erwin Brandstetter Mar 18 '21 at 21:31

2 Answers2

1

This will depend on the your database schema and the query you are executing. Only implementing both in your situation can give you the best answer about which one is good for you.

PostgreSQL have the ability to use multiple indexes in single query which is a great feature.

By quick googling I have found some very good answers on this topic. Please check below link: Multiple indexes vs single index on multiple columns in postgresql

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
1

In your case:

a query with where timestamp1 = 'sometimestamp' uses op 1 a query with where id13 = someid13 uses op 1

a query with where timestamp1 = 'sometimestamp' and id13 = someid13, may use op 2

a query with where id13 = someid13 and timestamp1 = 'sometimestamp' , may use op 1 or op 2

Why? because an index selection involves a lot of things, cardinality, size, stats, post select operations, etc.

And remember, op 2 is covering the first of op 1.

Frank N Stein
  • 2,219
  • 3
  • 22
  • 26
  • i have around 50 queries where i test my table..only 2 have queries like this with where timestamp1 = 'sometimestamp' and id13 = someid13, may use op 2 ...the rest use only timestamp1 in where clause...which option do you think i should choose? – xaroulis gekas Mar 18 '21 at 18:38
  • Plase, share the explain of your queries, the stats and the pg version, to see more in detail. one col index small is better than a multi col index – Frank N Stein Mar 18 '21 at 19:06