Re: [GENERAL] Unused indexes - PostgreSQL 9.2

Here is the url which explains the columns in pg_stat_all_indexes view http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW On Mon, May 23, 2016 at 8:42 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, May 23, 2016 at 8:33 PM, Lucas

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Mon, May 23, 2016 at 8:33 PM, Lucas Possamai wrote: > > > On 24 May 2016 at 12:18, Jeff Janes wrote: > >> On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai >> wrote: >> > >> > That index has been added just 3 hours ago, and you

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 24 May 2016 at 12:18, Jeff Janes wrote: > On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai > wrote: > > > > That index has been added just 3 hours ago, and you can see that the > > times_used goes over 41000 How is that possible?? > > Well, that

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai wrote: > > That index has been added just 3 hours ago, and you can see that the > times_used goes over 41000 How is that possible?? Well, that is what it is there for, right , to be used? My ancient laptop can use an

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

Hello Melvin, how are you doing? >> > And what happens if you run this query? > > SELECT idstat.schemaname AS schema, >idstat.relname AS table_name, >indexrelname AS index_name, >idstat.idx_scan AS times_used, >

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 11 May 2016 at 09:50, Melvin Davidson wrote: > My bad, WHERE indexrelname = ' {YOUR INDEX NAME } '; > > Oh! ok... public ja_feedlog ix_ja_feedlog_visitids 1 94 GB 1939 MB 0 CREATE INDEX "ix_ja_feedlog_visitids" ON "ja_feedlog" USING "btree" ("visitid") public

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

My bad, WHERE indexrelname = ' {YOUR INDEX NAME } '; If you put the name of your index, you should get back stats for it. What are those stats? On Tue, May 10, 2016 at 5:47 PM, Lucas Possamai wrote: > >> And what happens if you run this query? >> >> SELECT

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > > And what happens if you run this query? > > SELECT idstat.schemaname AS schema, >idstat.relname AS table_name, >indexrelname AS index_name, >idstat.idx_scan AS times_used, >pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) || > '.' ||

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 5:23 PM, Lucas Possamai wrote: > >>> >> >Some time ago I changed the pg_stat_temp directory from >> /var/lib/pgsq/whatever to /tmp >> Have you checked the postgres log to see if there are any errors about it >> not being able to write to the

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > >> > >Some time ago I changed the pg_stat_temp directory from > /var/lib/pgsq/whatever to /tmp > Have you checked the postgres log to see if there are any errors about it > not being able to write to the pg_stat_temp dir? > > Yep no errors =\

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 5:17 PM, Lucas Possamai wrote: > >> Sorry, I was too busy looking at the content. >> >> Has the size / # rows changed recently? If the planner thinks it can load >> all the rows faster, it will use a seqscan regardless if you have an index. >> >> If

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

> > > Sorry, I was too busy looking at the content. > > Has the size / # rows changed recently? If the planner thinks it can load > all the rows faster, it will use a seqscan regardless if you have an index. > > If that is the case, you can force index use by doing a > > SET enable_seqscan = off

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 5:06 PM, Lucas Possamai wrote: > >>> >> My crystal ball is not working, you have a PostgreSQL version? >> > > Maybe you should have a look on the subject of this email... > > >> >> in postgresql.conf are track_activities and track_counts both on? >>

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On 11 May 2016 at 09:06, Lucas Possamai wrote: > >>> >> My crystal ball is not working, you have a PostgreSQL version? >> > > Maybe you should have a look on the subject of this email... > > >> >> in postgresql.conf are track_activities and track_counts both on? >> > > yes

Re: [GENERAL] Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai wrote: > Hi all, > > I ran a query to search for unused indexes, and get some free space in my > DB: > > SELECT >> --*, >> relid::regclass AS table, >> indexrelid::regclass AS index, >>

[GENERAL] Unused indexes - PostgreSQL 9.2

Hi all, I ran a query to search for unused indexes, and get some free space in my DB: SELECT > --*, > relid::regclass AS table, > indexrelid::regclass AS index, > --pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS > index_size, >

[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

Re: [GENERAL] Unused Indexes

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

Re: [GENERAL] Unused Indexes

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

Re: [GENERAL] Unused Indexes

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

Re: [GENERAL] Unused Indexes

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 [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 13:46:46 To:[EMAIL PROTECTED] Subject: [GENERAL] Unused Indexes Hi, I have a table which I have

Re: [GENERAL] Unused Indexes

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