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 Possamai
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 can see that the
>> > times_used goes over 41000 How is that p
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 is what it is there for, right , to be used?
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 index that many times in le
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,
>pg_size_pretty(pg_relation_size(quote_ident(idstat.sch
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 ja_feedlog ix_ja_feedlog_visit
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 idstat.schemaname AS schema,
>>
>
>
> 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) ||
> '.' || quote_ident(i
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 pg_stat_temp dir?
>>
>>
> Yep..
>
>
>>
> >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 =\
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 that is the case, you
>
>
> 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
>
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?
>>
>
> yes
>
>
>>
>> Did
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
>
>
>>
>> Did you ANAL
>
>
>>
> 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
>
> Did you ANALYZE the table after you re-added the index?
>
Yes
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,
>> --pg_size_pretty(pg_relation_size(indexrelid::r
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 usin
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 p
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 where col2 = 1::bigint and col2 = 1::bigint
Tim McAuley wrote:
Hi
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
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
21 matches
Mail list logo