pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Seq scan vs index scan
5+ messages / 4 participants
[nested] [flat]

* Seq scan vs index scan
@ 2024-03-23 03:55 arun chirappurath <arunsnmimt@gmail.com>
  2024-03-23 04:02 ` Re: Seq scan vs index scan Christophe Pettus <xof@thebuild.com>
  2024-03-23 04:02 ` Re: Seq scan vs index scan David G. Johnston <david.g.johnston@gmail.com>
  2024-03-23 04:13 ` Re: Seq scan vs index scan Tom Lane <tgl@sss.pgh.pa.us>
  2024-03-23 04:21 ` Re: Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
  0 siblings, 4 replies; 5+ messages in thread

From: arun chirappurath @ 2024-03-23 03:55 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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-hi...

Thanks,
Arun

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Seq scan vs index scan
  2024-03-23 03:55 Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-23 04:02 ` Christophe Pettus <xof@thebuild.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Christophe Pettus @ 2024-03-23 04:02 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org



> On Mar 22, 2024, at 20:55, arun chirappurath <arunsnmimt@gmail.com> wrote:
> I am trying to force query to use indexes  using query hints.

PostgreSQL does not have query hints.  Enabling index scans using parameters doesn't *disable* other types of query nodes.

You can disable sequential scans using:

	SET enable_seqscan = off;

... but more importantly, why do you want to force an index scan?  Generally, PostgreSQL will pick a sequential scan over an index scan if the table is small, or the number of rows that come back from the query are a significant percentage of the rows of the table.

If you would like a more detailed answer, it would be a good idea to post an execution plan of the query with:

	EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'example_username';




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Seq scan vs index scan
  2024-03-23 03:55 Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-23 04:02 ` David G. Johnston <david.g.johnston@gmail.com>
  3 siblings, 0 replies; 5+ messages in thread

From: David G. Johnston @ 2024-03-23 04:02 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Fri, Mar 22, 2024 at 8:55 PM arun chirappurath <arunsnmimt@gmail.com>
wrote:

>
> I am trying to force query to use indexes  using query hints.
>
> Set enable indexscan to ON,
> Same for bitmap and index only scan
>

Everything is on by default in the planner.  You need to think in terms of
what you don't want to happen and disabled those things.

David J.

>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Seq scan vs index scan
  2024-03-23 03:55 Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-23 04:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2024-03-23 04:13 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

arun chirappurath <arunsnmimt@gmail.com> writes:
> I have a table named  users with index on user name.
> ...
> When I try to do below select query it's taking seq scan and query returns
> in 5ms.

5ms is an okay runtime, I would think.  Is the table empty?

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

The way to force it would be enable_seqscan = off.  I would not
be surprised to hear that that makes it slower, though.  The
planner will prefer not to use an index if it thinks that will
be slower, and in this case it sounds like that could be right.
(Don't ever assume that what the planner does with a small table
is what it will do with a large table.)

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Seq scan vs index scan
  2024-03-23 03:55 Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-23 04:21 ` arun chirappurath <arunsnmimt@gmail.com>
  3 siblings, 0 replies; 5+ messages in thread

From: arun chirappurath @ 2024-03-23 04:21 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Thanks Tom,David and Chris for detailed opinions

Regards,
Arun

On Sat, 23 Mar 2024 at 09:25, arun chirappurath <arunsnmimt@gmail.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-hi...
>
> Thanks,
> Arun
>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2024-03-23 04:21 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-23 03:55 Seq scan vs index scan arun chirappurath <arunsnmimt@gmail.com>
2024-03-23 04:02 ` Christophe Pettus <xof@thebuild.com>
2024-03-23 04:02 ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-23 04:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-23 04:21 ` arun chirappurath <arunsnmimt@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox