Re: Seq scan vs index scan

Thanks Tom,David and Chris for detailed opinions

Regards,
Arun

On Sat, 23 Mar 2024 at 09:25, arun chirappurath <arunsnmimt(at)gmail(dot)com>
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
>