On Tue, 3 May 2016 08:45 David G. Johnston,
wrote:
> I am not sure, but I think if the size of index is very huge and you
>> suspect they are not being used, you might want to check the bloats
>> percentage in the index. If this is true, perhaps a reindex might help.
>
> I am not sure, but I think if the size of index is very huge and you
> suspect they are not being used, you might want to check the bloats
> percentage in the index. If this is true, perhaps a reindex might help.
>
If they aren't being used just drop them. Its when they are used and get
On Tue, 3 May 2016 03:46 drum.lu...@gmail.com, wrote:
> The index that I've created and is working is:
>
> Index without typecasting:
>
>> CREATE INDEX CONCURRENTLY ix_clientids2 ON gorfs.inode_segments USING
>> btree (full_path);
>
>
> Thanks for the help, guys!
>
>
>
Yes, my bad, please add
AND NOT idx.indisunique
AND NOT indisexclusion
to the query that generates the DROPs.
Note that it only generates the SQL statement that does the drop, it does
not execute or actaully drop them.
On Mon, May 2, 2016 at 5:29 PM, Julien Rouhaud
On 02/05/2016 23:02, drum.lu...@gmail.com wrote:
>
> Generically speaking, if the total of dx_scan + idx_tup_read +
> idx_tup_fetch are 0, then it is an _indication_ that those indexes
> should be dropped.
> You should also consider how long those indexes have existed and how
>
>
>
> Generically speaking, if the total of dx_scan + idx_tup_read +
> idx_tup_fetch are 0, then it is an _indication_ that those indexes should
> be dropped.
> You should also consider how long those indexes have existed and how often
> queries are executed.
>
> A good practice would be to save
On Mon, May 2, 2016 at 4:08 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, May 2, 2016 at 12:56 PM, drum.lu...@gmail.com <
> drum.lu...@gmail.com> wrote:
>
>>
>>> Index size and index usage are unrelated. Modifications to the index
>>> to keep it in sync with the
On Mon, May 2, 2016 at 12:56 PM, drum.lu...@gmail.com
wrote:
>
>>>
>> Index size and index usage are unrelated. Modifications to the index to
>> keep it in sync with the table do not count as "usage" - only reading it
>> for where clause use counts.
>>
>>
>> So only
>
>
>>
> Index size and index usage are unrelated. Modifications to the index to
> keep it in sync with the table do not count as "usage" - only reading it
> for where clause use counts.
>
> David J.
>
>
>
So only those with* 0 size*, should be deleted? Is that you're saying?
Can you be
On Mon, May 2, 2016 at 12:44 PM, drum.lu...@gmail.com
wrote:
> Melvin, that Query you sent is very interesting..
>
> SELECT n.nspname as schema,
>>i.relname as table,
>>i.indexrelname as index,
>>i.idx_scan,
>>i.idx_tup_read,
>>
The index that I've created and is working is:
Index without typecasting:
> CREATE INDEX CONCURRENTLY ix_clientids2 ON gorfs.inode_segments USING
> btree (full_path);
Thanks for the help, guys!
Melvin, that Query you sent is very interesting..
SELECT n.nspname as schema,
>i.relname
On Mon, May 2, 2016 at 8:16 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Sun, May 1, 2016 at 7:27 PM, drum.lu...@gmail.com > wrote:
>
> Repeating the query to improve the self-containment aspect of the email
> would have been appreciated.
>
>
>>
>> if
On Sun, May 1, 2016 at 7:27 PM, drum.lu...@gmail.com
wrote:
Repeating the query to improve the self-containment aspect of the email
would have been appreciated.
>
> if possible please have a look on the explain analyze results:
>
> http://explain.depesz.com/s/rHOU
>
>
On Sun, May 1, 2016 at 10:27 PM, drum.lu...@gmail.com
wrote:
> Sorry @Melvin, sent the previous email just to you..
>
>
> That's a great one, too! Cheers!
>
>
> Well.. the index creation did not help...
>
> if possible please have a look on the explain analyze results:
>
>
Sorry @Melvin, sent the previous email just to you..
That's a great one, too! Cheers!
Well.. the index creation did not help...
if possible please have a look on the explain analyze results:
http://explain.depesz.com/s/rHOU
What else can I do?
*The indexes I created is:*
- CREATE INDEX
On Sun, May 1, 2016 at 9:18 PM, drum.lu...@gmail.com
wrote:
> To clarify, the index is based on a function called "split_part()
>> The WHERE clause is only referencing the full_part column, so the planner
>> cannot associate the index with the full_part column.
>>
>
>
>
> To clarify, the index is based on a function called "split_part()
> The WHERE clause is only referencing the full_part column, so the planner
> cannot associate the index with the full_part column.
>
Thanks for the explanation, Melvin.
It would be simple like:
CREATE INDEX CONCURRENTLY
On Sun, May 1, 2016 at 6:31 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Sunday, May 1, 2016, Melvin Davidson wrote:
>
>>
>> Your index is based on split_part function
>> but the WHERE clause is specific to full_path, so the planner cannot find
>> a valid
On Sun, May 1, 2016 at 5:58 PM, drum.lu...@gmail.com
wrote:
>
>>
>> Well, a little more information would be useful like:
>>
>
> Ops.. yes sure.. sorry about that.
>
>
>> 1. What is the PostgreSQL version?
>>
>
> PostgreSQL 9.2
>
>
>> 2. What is the O/S?
>>
>
> Linux Centos
>
>
>
> Well, a little more information would be useful like:
>
Ops.. yes sure.. sorry about that.
> 1. What is the PostgreSQL version?
>
PostgreSQL 9.2
> 2. What is the O/S?
>
Linux Centos 6.7 64 bits
> 3. What is the structure of gorfs.inode_segments?
>
Table inode_segments: (I'll
On Sunday, May 1, 2016, drum.lu...@gmail.com wrote:
> Hi all,
>
> I've got the following index on the gorfs.inode_segments table:
>
>>
>> CREATE INDEX ix_clientids
>> ON gorfs.inode_segments
>> USING btree
>> (("split_part"("full_path"::"text", '/'::"text",
On Sun, May 1, 2016 at 5:40 PM, drum.lu...@gmail.com
wrote:
> Hi all,
>
> I've got the following index on the gorfs.inode_segments table:
>
>>
>> CREATE INDEX ix_clientids
>> ON gorfs.inode_segments
>> USING btree
>> (("split_part"("full_path"::"text", '/'::"text",
Hi all,
I've got the following index on the gorfs.inode_segments table:
>
> CREATE INDEX ix_clientids
> ON gorfs.inode_segments
> USING btree
> (("split_part"("full_path"::"text", '/'::"text", 4)::integer))
> WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text");
And I'm running
Ok. I have been working with databases a few years but my first real venture in
to PostgreSql. I just want a plain simple index regardless if there are
duplicates or not. How do I accomplish this in PostgreSql?
Michael
On Mar 2, 2011, at 11:31 AM, Michael Black wrote:
Ok. I have been working with databases a few years but my first real venture
in to PostgreSql. I just want a plain simple index regardless if there are
duplicates or not. How do I accomplish this in PostgreSql?
Same as any other SQL
On Wed, Mar 02, 2011 at 06:31:57PM +, Michael Black wrote:
Ok. I have been working with databases a few years but my first real venture
in to PostgreSql. I just want a plain simple index regardless if there are
duplicates or not. How do I accomplish this in PostgreSql?
CREATE
On Mar 2, 2011, at 11:43 AM, Michael Black wrote:
Thanks Scott. I just did not see the options in the PGAdmin III nor in the
doc at
You may want to bookmark this:
http://www.postgresql.org/docs/9.0/static/sql-commands.html
--
Scott Ribe
scott_r...@elevated-dev.com
On Wed, 2011-03-02 at 13:45 -0500, Andrew Sullivan wrote:
On Wed, Mar 02, 2011 at 06:31:57PM +, Michael Black wrote:
Ok. I have been working with databases a few years but my first real
venture in to PostgreSql. I just want a plain simple index regardless if
there are duplicates
Thank you for the links.
Subject: Re: [GENERAL] Index question
From: j...@commandprompt.com
To: a...@crankycanuck.ca
CC: pgsql-general@postgresql.org
Date: Wed, 2 Mar 2011 11:05:58 -0800
On Wed, 2011-03-02 at 13:45 -0500, Andrew Sullivan wrote:
On Wed, Mar 02, 2011 at 06:31:57PM +
On Thu, Jan 7, 2010 at 11:11 PM, akp geek akpg...@gmail.com wrote:
I have query in production and test. The tables in both the environment has
the same structure ,indexes and constraints. But the in the test and the
prod the explain plan is totally different. In test environment the query is
The volume of data is less in Test compared to prod. and I synced the
postgresql.conf file in both environments
Regards
On Fri, Jan 8, 2010 at 1:55 PM, Vick Khera vi...@khera.org wrote:
On Thu, Jan 7, 2010 at 11:11 PM, akp geek akpg...@gmail.com wrote:
I have query in production and test.
there ya go. the query plan will change based on the data statistics
on the tables and indexes.
On Fri, Jan 8, 2010 at 2:09 PM, akp geek akpg...@gmail.com wrote:
The volume of data is less in Test compared to prod. and I synced the
postgresql.conf file in both environments
--
Sent via
OK.. got you.
Regards
On Fri, Jan 8, 2010 at 2:37 PM, Vick Khera vi...@khera.org wrote:
there ya go. the query plan will change based on the data statistics
on the tables and indexes.
On Fri, Jan 8, 2010 at 2:09 PM, akp geek akpg...@gmail.com wrote:
The volume of data is less in Test
From: akp geek [mailto:akpg...@gmail.com]
Sent: Thursday, January 07, 2010 9:04 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
{snip}
Why would the index I have created not being used?
The index you have created will not be used
Hi All -
I have query in production and test. The tables in both the
environment has the same structure ,indexes and constraints. But the in the
test and the prod the explain plan is totally different. In test environment
the query is taking long time and noticed that indexes are
-general@postgresql.org
Subject: [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables in both
the environment has the same structure ,indexes and constraints. But the
in the test and the prod the explain plan is totally different
On Thu, Jan 7, 2010 at 9:11 PM, akp geek akpg...@gmail.com wrote:
Hi All -
I have query in production and test. The tables in both the
environment has the same structure ,indexes and constraints. But the in the
test and the prod the explain plan is totally different. In test
...@postgresql.org [mailto:
pgsql-general-ow...@postgresql.org] *On Behalf Of *akp geek
*Sent:* Thursday, January 07, 2010 8:11 PM
*To:* pgsql-general@postgresql.org
*Subject:* [GENERAL] Index question on postgres
Hi All -
I have query in production and test. The tables
Post the results here
From: akp geek [mailto:akpg...@gmail.com]
Sent: Thursday, January 07, 2010 8:30 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11
:* Thursday, January 07, 2010 8:30 PM
*To:* Dann Corbit
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] Index question on postgres
I did that and the Explain look different
Regards
On Thu, Jan 7, 2010 at 11:13 PM, Dann Corbit dcor...@connx.com wrote:
If you do a:
VACUUM
On Thu, Jan 7, 2010 at 10:04 PM, akp geek akpg...@gmail.com wrote:
the explain from both enviroments ??? need to be posted.
just one quick question. Why would the index I have created not being used?
Regards
Better explain analyze than plain eplain.
If the retrieval of data by seq scan is
I have created a table where a unique row is defined by 5 columns and
have created an index on these 5 columns. If I then do a select with a
where clause that only consists of 3 of these columns, will I get the
speed benefits from the index that I have created, or will I need to
create another
Bierbryer, Andrew abierbr...@klsdiversified.com writes:
I have created a table where a unique row is defined by 5 columns and
have created an index on these 5 columns. If I then do a select with a
where clause that only consists of 3 of these columns, will I get the
speed benefits from the
Hi guys, I'm very new to PostgreSQL so please excuse me if this is an
easy question..
I have a table called Recipes which has a column called CookTime. I
have an index on the CookTime column as such:
CREATE INDEX idx_recipes_cooktime
ON recipes
USING btree
(cooktime);
If I run the
On Wed, Oct 1, 2008 at 1:57 PM, Mike Christensen [EMAIL PROTECTED] wrote:
Hi guys, I'm very new to PostgreSQL so please excuse me if this is an easy
question..
I have a table called Recipes which has a column called CookTime. I have
an index on the CookTime column as such:
CREATE INDEX
Mike Christensen [EMAIL PROTECTED] writes:
As you can see the index is not being used and it's doing a seq scan
on the table directly. I would think if Postgres is indeed keeping a
btree index on the column, meaning the values would be stored in
numerical order, the index would be used
Thanks Tom!
I just tried a query for cooktimes over 1 million to test your theory,
as it would almost instantly be able to tell from the index that there
are zero rows matching that condition. Indeed, it hits the index
which is what I would expect, and the total runtime is 0.163ms.
I have a query which orders data like this: ORDER BY a.col1, b.col2 where a
and b are different tables. Both tables are really big and so, ordering takes
quite a while. Now i would like to create an index to speed things up, but i
don't know how to create such an index that would cover both
At 04:39 PM 6/7/2004 +0200, Jernej Kos wrote:
I have a query which orders data like this: ORDER BY a.col1, b.col2 where a
and b are different tables. Both tables are really big and so, ordering takes
quite a while. Now i would like to create an index to speed things up, but i
don't know how to
I have heard that postgres will not use an index
unless the field has a not null constraint on it. Is that
true?
Rick Gigger wrote:
I have heard that postgres will not use an index unless the field has
a not null constraint on it. Is that true?
I have never heard that. There are some oddities with using an Index.
For example,
if you are using a bigint you need to '' the value or if you are using
an
Is there any reason an index would not be used in optimising a query?
I have an SQL which does a join and correctly picks up an index on a numeric
column
but fails to use another index that is on a character column (char I think)
Maybe its because its character column - as most joins are done
' AND
c.oid = i.indrelid AND
i.indexrelid = c2.oid
ORDER BY
c2.relname;
Hope this helps
Francis Solomon
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of
[EMAIL PROTECTED]
Sent: 08 December 2000 11:02
To: [EMAIL PROTECTED]
Subject: [GENERAL] Index
53 matches
Mail list logo