Seq scan vs index scan

Поиск
Список
Период
Сортировка
От arun chirappurath
Тема Seq scan vs index scan
Дата
Msg-id CAA23SdtXY94TGASWxVAV9ffx0H3GXBR7fuQnT_Ba20GZsbkZ=w@mail.gmail.com
обсуждение исходный текст
Ответы Re: Seq scan vs index scan
Re: Seq scan vs index scan
Re: Seq scan vs index scan
Re: Seq scan vs index scan
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: PostgreSQL as advanced job queuing system
Следующее
От: Christophe Pettus
Дата:
Сообщение: Re: Seq scan vs index scan