pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Unused Indexes
13+ messages / 10 participants
[nested] [flat]

* Unused Indexes
@ 2003-07-30 12:46 Tim McAuley <mcauleyt@tcd.ie>
  2003-07-30 13:13 ` Re: Unused Indexes Mike Mascari <mascarm@mascari.com>
  2003-07-30 13:26 ` Re: Unused Indexes DeJuan Jackson <djackson@speedfc.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Tim McAuley @ 2003-07-30 12:46 UTC (permalink / raw)
  To: pgsql-general

Hi,

I have a table which I have populated with over 5000 entries. There is a 
combined  index placed on two of the columns (both bigint). I am trying 
a simple select (i.e. select id where col1 = 1 and col2 = 1) covering 
these  two columns and it keeps using a seq scan. Is this correct? I 
would have thought that with this number of entries that an index scan 
should be used.

I am testing this using postgresql 7.3.3 on windows 2000 using cygwin.

Doing "set enable_seqscan to off" does not change the results of the 
explain operation.

I also tried setting a single index on just one of the columns and 
running an appropriate search; it still uses a seq scan. At what stage 
will the planner normally start using an index scan?

Any hints appreciated.

Tim








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

* Re: Unused Indexes
  2003-07-30 12:46 Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
@ 2003-07-30 13:13 ` Mike Mascari <mascarm@mascari.com>
  2003-07-30 13:16   ` Re: Unused Indexes Mike Mascari <mascarm@mascari.com>
  2003-07-30 14:06   ` Re: Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
  1 sibling, 2 replies; 13+ messages in thread

From: Mike Mascari @ 2003-07-30 13:13 UTC (permalink / raw)
  To: Tim McAuley <mcauleyt@tcd.ie>; +Cc: pgsql-general

Tim McAuley wrote:
> Hi,
> 
> I have a table which I have populated with over 5000 entries. There is a
> combined  index placed on two of the columns (both bigint). I am trying
> a simple select (i.e. select id where col1 = 1 and col2 = 1) covering
> these  two columns and it keeps using a seq scan. Is this correct? I
> would have thought that with this number of entries that an index scan
> should be used.

You must cast the 1 to a bigint:

SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint

This should probably be listed under FAQ 4.8, but it isn't.

Hope that helps,

Mike Mascari
mascarm@mascari.com






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

* Re: Unused Indexes
  2003-07-30 12:46 Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
  2003-07-30 13:13 ` Re: Unused Indexes Mike Mascari <mascarm@mascari.com>
@ 2003-07-30 13:16   ` Mike Mascari <mascarm@mascari.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Mike Mascari @ 2003-07-30 13:16 UTC (permalink / raw)
  To: Mike Mascari <mascarm@mascari.com>; +Cc: Tim McAuley <mcauleyt@tcd.ie>; pgsql-general

Mike Mascari wrote:

> Tim McAuley wrote:
> 
>>Hi,
>>
>>I have a table which I have populated with over 5000 entries. There is a
>>combined  index placed on two of the columns (both bigint). I am trying
>>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering
>>these  two columns and it keeps using a seq scan. Is this correct? I
>>would have thought that with this number of entries that an index scan
>>should be used.
> 
> 
> You must cast the 1 to a bigint:
> 
> SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint

That should read:

SELECT id WHERE col1 = 1::bigint AND col2 = 1::bigint.

My dyslexia is kicking in...

Mike Mascari
mascarm@mascari.com





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

* Re: Unused Indexes
  2003-07-30 12:46 Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
  2003-07-30 13:13 ` Re: Unused Indexes Mike Mascari <mascarm@mascari.com>
@ 2003-07-30 14:06   ` Tim McAuley <mcauleyt@tcd.ie>
  1 sibling, 0 replies; 13+ messages in thread

From: Tim McAuley @ 2003-07-30 14:06 UTC (permalink / raw)
  To: Mike Mascari <mascarm@mascari.com>; +Cc: pgsql-general

Mike Mascari wrote:

>Tim McAuley wrote:
>  
>
>>Hi,
>>
>>I have a table which I have populated with over 5000 entries. There is a
>>combined  index placed on two of the columns (both bigint). I am trying
>>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering
>>these  two columns and it keeps using a seq scan. Is this correct? I
>>would have thought that with this number of entries that an index scan
>>should be used.
>>    
>>
>
>You must cast the 1 to a bigint:
>
>SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint
>
>This should probably be listed under FAQ 4.8, but it isn't.
>  
>
That's it!

I had actually just come across that before reading this email. It gets 
the explain back into shape anyway!

Thanks!

Tim





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

* Re: Unused Indexes
  2003-07-30 12:46 Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
@ 2003-07-30 13:26 ` DeJuan Jackson <djackson@speedfc.com>
  1 sibling, 0 replies; 13+ messages in thread

From: DeJuan Jackson @ 2003-07-30 13:26 UTC (permalink / raw)
  To: Tim McAuley <mcauleyt@tcd.ie>; +Cc: pgsql-general

Assuming you have done a 'VACUUM ANALYZE' on the table in question you 
are most likely running into a type coercion issue.
So explicitly cast your constants to bigint and the index should start 
being considered.

  select id from <table> where col2 = 1::bigint and col2 = 1::bigint

Tim McAuley wrote:

> Hi,
>
> I have a table which I have populated with over 5000 entries. There is 
> a combined  index placed on two of the columns (both bigint). I am 
> trying a simple select (i.e. select id where col1 = 1 and col2 = 1) 
> covering these  two columns and it keeps using a seq scan. Is this 
> correct? I would have thought that with this number of entries that an 
> index scan should be used.
>
> I am testing this using postgresql 7.3.3 on windows 2000 using cygwin.
>
> Doing "set enable_seqscan to off" does not change the results of the 
> explain operation.
>
> I also tried setting a single index on just one of the columns and 
> running an appropriate search; it still uses a seq scan. At what stage 
> will the planner normally start using an index scan?
>
> Any hints appreciated.
>
> Tim
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster






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

* Re: Unused Indexes
@ 2003-07-30 13:49 EDMUND DENGLER <edmundd@esentire.com>
  0 siblings, 0 replies; 13+ messages in thread

From: EDMUND DENGLER @ 2003-07-30 13:49 UTC (permalink / raw)
  To: mcauleyt@tcd.ie

You need to convert the int's to bigints.

select id where col1 = 1::bigint and col2 = 1::bigint

Regards,
Ed

-----Original Message-----
From: Tim McAuley <mcauleyt@tcd.ie>
Date: Wed, 30 Jul 2003 13:46:46 
To:pgsql-general@postgresql.org
Subject: [GENERAL] Unused Indexes

Hi,

I have a table which I have populated with over 5000 entries. There is a 
combined  index placed on two of the columns (both bigint). I am trying 
a simple select (i.e. select id where col1 = 1 and col2 = 1) covering 
these  two columns and it keeps using a seq scan. Is this correct? I 
would have thought that with this number of entries that an index scan 
should be used.

I am testing this using postgresql 7.3.3 on windows 2000 using cygwin.

Doing "set enable_seqscan to off" does not change the results of the 
explain operation.

I also tried setting a single index on just one of the columns and 
running an appropriate search; it still uses a seq scan. At what stage 
will the planner normally start using an index scan?

Any hints appreciated.

Tim






---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



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

* Unused indexes
@ 2018-12-01 11:24 Ron <ronljohnsonjr@gmail.com>
  2018-12-01 12:42 ` Re: Unused indexes Rene Romero Benavides <rene.romero.b@gmail.com>
  2018-12-01 12:51 ` Re: Unused indexes Martín Marqués <martin.marques@gmail.com>
  0 siblings, 2 replies; 13+ messages in thread

From: Ron @ 2018-12-01 11:24 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

For indexes that existed before the cluster was last started, and for which 
REINDEX hasn't been run on them since the cluster was last started... is it 
valid to say that an index has not been used since the cluster was started 
if these three pg_stat_all_indexes fields all have a value of 0?

idx_scan
idx_tup_read
idx_tup_fetch

If it matters, the version is 9.6.6.

Thanks

-- 
Angular momentum makes the world go 'round.




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

* Re: Unused indexes
  2018-12-01 11:24 Unused indexes Ron <ronljohnsonjr@gmail.com>
@ 2018-12-01 12:42 ` Rene Romero Benavides <rene.romero.b@gmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Rene Romero Benavides @ 2018-12-01 12:42 UTC (permalink / raw)
  To: ronljohnsonjr@gmail.com; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

I think your assumption is correct, as long as the statistics collector is
working correctly (I've never seen this not being the case), and the
setting "track_counts" is set to on.

Am Sa., 1. Dez. 2018 um 05:24 Uhr schrieb Ron <ronljohnsonjr@gmail.com>:

> For indexes that existed before the cluster was last started, and for
> which
> REINDEX hasn't been run on them since the cluster was last started... is
> it
> valid to say that an index has not been used since the cluster was started
> if these three pg_stat_all_indexes fields all have a value of 0?
>
> idx_scan
> idx_tup_read
> idx_tup_fetch
>
> If it matters, the version is 9.6.6.
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>

-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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

* Re: Unused indexes
  2018-12-01 11:24 Unused indexes Ron <ronljohnsonjr@gmail.com>
@ 2018-12-01 12:51 ` Martín Marqués <martin.marques@gmail.com>
  1 sibling, 0 replies; 13+ messages in thread

From: Martín Marqués @ 2018-12-01 12:51 UTC (permalink / raw)
  To: ronljohnsonjr@gmail.com; +Cc: pgsql-general@lists.postgresql.org

El sáb., 1 dic. 2018 a las 8:24, Ron (<ronljohnsonjr@gmail.com>) escribió:
>
> For indexes that existed before the cluster was last started, and for which
> REINDEX hasn't been run on them since the cluster was last started... is it
> valid to say that an index has not been used since the cluster was started
> if these three pg_stat_all_indexes fields all have a value of 0?
>
> idx_scan

Just this one, and that the index doesn't force a constraint (it's not
a unique index).

One other thing to keep in mind is that, if you have hot_standby
replicas, the index might be used there, and the primary doesn't have
information of index_scans on other nodes of the cluster.

Regards,

-- 
Martín Marqués
It’s not that I have something to hide,
it’s that I have nothing I want you to see




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

* Unused indexes
@ 2024-02-05 14:01 arun chirappurath <arunsnmimt@gmail.com>
  2024-02-05 16:03 ` Re: Unused indexes Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: arun chirappurath @ 2024-02-05 14:01 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi All,

Do we have a script to get unused indexes for 30 days and once identified
do we have an option to disable  and enable when required?

I sql server we have this option to disable it and need to rebuild it to
ensemble it

Thanks,
Arun

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

* Re: Unused indexes
  2024-02-05 14:01 Unused indexes arun chirappurath <arunsnmimt@gmail.com>
@ 2024-02-05 16:03 ` Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-06 02:53   ` Re: Unused indexes Greg Sabino Mullane <htamfids@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Ron Johnson @ 2024-02-05 16:03 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Mon, Feb 5, 2024 at 9:02 AM arun chirappurath <arunsnmimt@gmail.com>
wrote:

> Hi All,
>
> Do we have a script to get unused indexes for 30 days and once identified
> do we have an option to disable  and enable when required?
>

The pg_stat_*_tables tables idx_* columns has accumulated usage since the
last time you started the postmaster.


> I sql server we have this option to disable it and need to rebuild it to
> ensemble it
>

Sadly, PG does not have ALTER INDEX ... DISABLE;.

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

* Re: Unused indexes
  2024-02-05 14:01 Unused indexes arun chirappurath <arunsnmimt@gmail.com>
  2024-02-05 16:03 ` Re: Unused indexes Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-06 02:53   ` Greg Sabino Mullane <htamfids@gmail.com>
  2024-02-06 09:32     ` Re: Unused indexes Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Greg Sabino Mullane @ 2024-02-06 02:53 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: arun chirappurath <arunsnmimt@gmail.com>; pgsql-general@lists.postgresql.org

>
> The pg_stat_*_tables tables idx_* columns has accumulated usage since the
> last time you started the postmaster.
>

Actually, those persist at restart - you can use

 select datname, stats_reset from pg_stat_database;

to see when/if they were reset. You can look for zero/low entries in
pg_stat_user_indexes.idx_scan to find unused indexes. Note that replicas
maintain their own stats, so checking only the primary may cause a false
positive.


> I sql server we have this option to disable it and need to rebuild it to
>> ensemble it
>>
>
> Sadly, PG does not have ALTER INDEX ... DISABLE;.
>

Not really sure what the purpose of that is in sql server, but Ron is
correct, we have nothing equivalent. General usage in Postgres is to drop
the index if it is unused. If you need to create it again, easy enough with
CREATE INDEX CONCURRENTLY. Keeping your schema changes in a VCS (e.g. git)
is a good way to document when and why the index was dropped. I suppose in
a pinch you could keep the old index around by sticking it in a table
comment.

Cheers,
Greg

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

* Re: Unused indexes
  2024-02-05 14:01 Unused indexes arun chirappurath <arunsnmimt@gmail.com>
  2024-02-05 16:03 ` Re: Unused indexes Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-06 02:53   ` Re: Unused indexes Greg Sabino Mullane <htamfids@gmail.com>
@ 2024-02-06 09:32     ` Ron Johnson <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Ron Johnson @ 2024-02-06 09:32 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On Mon, Feb 5, 2024 at 9:54 PM Greg Sabino Mullane <htamfids@gmail.com>
wrote:

> The pg_stat_*_tables tables idx_* columns has accumulated usage since the
>> last time you started the postmaster.
>>
>
> Actually, those persist at restart - you can use
>
>  select datname, stats_reset from pg_stat_database;
>
> to see when/if they were reset. You can look for zero/low entries in
> pg_stat_user_indexes.idx_scan to find unused indexes. Note that replicas
> maintain their own stats, so checking only the primary may cause a false
> positive.
>
>
>> I sql server we have this option to disable it and need to rebuild it to
>>> ensemble it
>>>
>>
>> Sadly, PG does not have ALTER INDEX ... DISABLE;.
>>
>
> Not really sure what the purpose of that is in sql server,
>

To tell the system to stop using a specific index without having to drop
the index.

Its only purpose is to make the DBA's life easier.  IMNSHO, that's an
excellent reason to have such a feature.


> but Ron is correct, we have nothing equivalent. General usage in Postgres
> is to drop the index if it is unused. If you need to create it again, easy
> enough with CREATE INDEX CONCURRENTLY.
>

Unless it's blocked by existing readers.  I've seen that more than a few
times.


> Keeping your schema changes in a VCS (e.g. git) is a good way to document
> when and why the index was dropped. I suppose in a pinch you could keep the
> old index around by sticking it in a table comment.
>

The ALTER INDEX ... DISABLE / ALTER INDEX ... REBUILD pair ensures that
Mistakes Were Not Made.

You *can't* make the mistake of re-creating an index incorrectly if you
didn't actually drop the index.

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


end of thread, other threads:[~2024-02-06 09:32 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-30 12:46 Unused Indexes Tim McAuley <mcauleyt@tcd.ie>
2003-07-30 13:13 ` Mike Mascari <mascarm@mascari.com>
2003-07-30 13:16   ` Mike Mascari <mascarm@mascari.com>
2003-07-30 14:06   ` Tim McAuley <mcauleyt@tcd.ie>
2003-07-30 13:26 ` DeJuan Jackson <djackson@speedfc.com>
2003-07-30 13:49 Re: Unused Indexes EDMUND DENGLER <edmundd@esentire.com>
2018-12-01 11:24 Unused indexes Ron <ronljohnsonjr@gmail.com>
2018-12-01 12:42 ` Re: Unused indexes Rene Romero Benavides <rene.romero.b@gmail.com>
2018-12-01 12:51 ` Re: Unused indexes Martín Marqués <martin.marques@gmail.com>
2024-02-05 14:01 Unused indexes arun chirappurath <arunsnmimt@gmail.com>
2024-02-05 16:03 ` Re: Unused indexes Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-06 02:53   ` Re: Unused indexes Greg Sabino Mullane <htamfids@gmail.com>
2024-02-06 09:32     ` Re: Unused indexes Ron Johnson <ronljohnsonjr@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