From: | arun chirappurath <arunsnmimt(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Seq scan vs index scan |
Date: | 2024-03-23 03:55:16 |
Message-ID: | CAA23SdtXY94TGASWxVAV9ffx0H3GXBR7fuQnT_Ba20GZsbkZ=w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
Thanks,
Arun
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2024-03-23 04:02:18 | Re: Seq scan vs index scan |
Previous Message | Merlin Moncure | 2024-03-23 02:12:36 | Re: PostgreSQL as advanced job queuing system |