Postgres SQL does full table scan despite indexing - why? How to improve performance in any other way?

Question

I have a table that has a date value in column A and other columns B,C,D. I wanted to update the values of new column E based on the values of B,C,D for a given time period from A. It was slow so I added an index to A with the idea of avoiding a full table scan during the where clause A>date A<date. Still in explain and analyze I see a full table scan and the time is not being optimized as well. What to do?

Lets say the table is Table_1 and the query is:

 Update Table_1
 set E = CASE WHEN B>5 THEN B*2 ELSE 0
 WHERE C>6 and D>8
 AND A<'2024-01-01' and A>'2023-01-31'

Lets say A has data from 2000 to 2024 and using the index on A I dont want a full scan.

• jjanes: It will use an index on A if it think that that will be useful. It will depend on how much of the table is thought to match the condition on A. If most of the table matches, then using the index will be counter-productive, and it will not use it.
• user1937198: What is the actual query plan if possible, and especially what is the total number of rows in the table, expeced number of returned rows actual number of returned rows? The relationship between these three numbers should be informative as to why postgres prefers a tablescan.

Answer 1

Have you tried include(B,C,D,E). Maybe the full scan is a result of a lookup. Forgive me if I'm off. I'm newer to postgres, coming from a sql server background.

• topsail: I'm also from a sql server background and I can assure you we don't know about indexing in postgres - better to stay silent, listen and learn in this case.

Answer 2

You are using all the columns of a table to filter, so obviously any query processor will be using tablescan. If you want your query processor to use Index, you should add all the columns to an Index. Query will perform a little better as the data in index will be sorted than a table data.

• user1937198: If one of the filters has sufficiently high selectivity, postgres is perfectly happy to filter initially by an index and then subsequently by additional filters.