3

I have a question about update/delete statements in order to optimize queries in my app.

Does it ever make sense to add something else to a where clause in order to speed up the query if we already have a primary key in the clause?

Is it ever worth to add other indexes in the statements (besides the primary key)? It will have an impact on query performance? Probably a negative impact because of additional step? Am I right?

Is it ever worth to indicate a specific partition in the where clause (besides the primary key) if we know which partition the record is on?

Will it speed up the query or slow it down, because the query will use the B-Tree (primary key) in the first step to search for the given record and no need other steps?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
George
  • 49
  • 4
  • Such general question is *off topic*. Please post your table DDL incl. index definition. This is *higly* relevant, e.g. if you have a *local* index, you will profit from *partition pruning*. The *first* thing you should learn how to get and read is the [execution plan](https://stackoverflow.com/a/34975420/4808122) of your query. – Marmite Bomber Feb 15 '22 at 18:13
  • Is the primary key only used in equality conditions? – Jon Heller Feb 15 '22 at 23:41

1 Answers1

-2

the where clause is only there to delimit the SQL statement. The primary key is only responsible for non-double scoring

I tested it:

a table with primary-key: 4,5 sec without primary-key: 3,8 sec

KleeSchulz
  • 1
  • 3
  • 1
    Adding indexes _may_ (or may not) help with the performance of a given SELECT statement, but they will _always_ create more overhead for INSERT, UPDATE, and DELETE, due to the added work of maintaining the indexes with the inserted, updated, or deleted data. – EdStevens Feb 14 '22 at 14:21