Re: Seq scan vs index scan

[arun chirappurath <arunsnmimt@xxxxxxxxx>]

Thanks Tom,David and Chris for detailed opinions

Regards,

Arun

On Sat, 23 Mar 2024 at 09:25, arun chirappurath <arunsnmimt@xxxxxxxxx> wrote:

Hi All,

I have a table named  users with index on user name.

CREATE TABLE users (

    user_id SERIAL PRIMARY KEY,

    username VARCHAR(50) NOT NULL,

    email VARCHAR(100) UNIQUE NOT NULL,

    age INT

);

CREATE INDEX idx_username ON users (username);

When I try to do below select query it's taking seq scan and query returns in 5ms.

SELECT * FROM users WHERE username = 'example_username';

I am trying to force query to use indexes  using query hints.

Set enable indexscan to ON,

Same for bitmap and index only scan

and ran the query. 

However it still uses seq scan instead of index scan.

1. Is there a way to force query to use an index? With out changing default settings of postgres rds

 2. Modifying random page cost is desired the way or hint extension? In which case do we use this?will it affect selecting index for all queries

3.i have done analyze on the table and tried recreating index..why is it still taking seq scan?

In Sql server we can force query just by proving it directly in query.

USE AdventureWorks

GO

SELECT c.ContactID

FROM Person.Contact c

WITH (INDEX(AK_Contact_rowguid))

INNER JOIN Person.Contact pc

WITH (INDEX(PK_Contact_ContactID))

ON c.ContactID = pc.ContactID

GO

https://blog.sqlauthority.com/2009/02/07/sql-server-introduction-to-force-index-query-hints-index-hint/

Thanks,

Arun