Re: [GENERAL] index question

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. >> > > If they aren't being u

Re: [GENERAL] index question

> > 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 bloat

Re: [GENERAL] index question

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! > > > Melvin, that Query you sent

Re: [GENERAL] index question

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 wrote: > On 02/05/2016 23:0

Re: [GENERAL] index question

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 >

Re: [GENERAL] index question

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

Re: [GENERAL] index question

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 table

Re: [GENERAL] index question

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 those with* 0 size*, should

Re: [GENERAL] index question

> > >> > ​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 more

Re: [GENERAL] index question

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, >>i.idx_tup_fetch, >>CA

Re: [GENERAL] index question

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 a

Re: [GENERAL] index question

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 possible please have a

Re: [GENERAL] index question

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 > > What else can I do? > >

Re: [GENERAL] index question

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: > > http://explain.depesz.c

Re: [GENERAL] index question

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 CONC

Re: [GENERAL] index question

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. >> > > Thanks for the explanati

Re: [GENERAL] index question

> > 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 O

Re: [GENERAL] index question

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 index >> >> > > > Davi

Re: [GENERAL] index question

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 index > > This sentence is even less useful than the questions that you asked... David J.

Re: [GENERAL] index question

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 6.7 64 bits > > >> 3.

Re: [GENERAL] index question

> > > > 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 leave

Re: [GENERAL] index question

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", 4)::integer)) >> WHERE "gorfs".

Re: [GENERAL] index question

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", 4)::integer)) >> WHERE

[GENERAL] index question

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 th

Re: [GENERAL] Index question

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

Re: [GENERAL] Index question

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 dupl

Re: [GENERAL] Index question

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: -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-d

Re: [GENERAL] Index question

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 I

Re: *****SPAM***** [GENERAL] Index question

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 data

[GENERAL] Index question

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

Re: [GENERAL] Index question on postgres

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 u

Re: [GENERAL] Index question on postgres

OK.. got you. Regards On Fri, Jan 8, 2010 at 2:37 PM, Vick Khera 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 wrote: > > The volume of data is less in Test compared to prod. and I synced

Re: [GENERAL] Index question on postgres

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 wrote: > The volume of data is less in Test compared to prod. and I synced the > postgresql.conf file in both environments -- Sent via pgsql-general mailing

Re: [GENERAL] Index question on postgres

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 wrote: > On Thu, Jan 7, 2010 at 11:11 PM, akp geek wrote: > > I have query in production and test. The tables in both the environme

Re: [GENERAL] Index question on postgres

On Thu, Jan 7, 2010 at 11:11 PM, akp geek 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 > taking long tim

Re: [GENERAL] Index question on postgres

On Thu, Jan 7, 2010 at 10:04 PM, akp geek 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 deemed "cheaper" th

Re: [GENERAL] Index question on postgres

; *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:13 P

Re: [GENERAL] Index question on postgres

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

Re: [GENERAL] Index question on postgres

> *From:* pgsql-general-ow...@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 - > > > &

Re: [GENERAL] Index question on postgres

On Thu, Jan 7, 2010 at 9:11 PM, akp geek 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 environment > the

Re: [GENERAL] Index question on postgres

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

[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. In test environment the query is taking long time and noticed that indexes are no

Re: [GENERAL] Index Question

"Bierbryer, Andrew" 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 index that I have created,

[GENERAL] Index Question

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 inde

Re: [GENERAL] Index question regarding numeric operators

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

Re: [GENERAL] Index question regarding numeric operators

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

Re: [GENERAL] Index question regarding numeric operators

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

[GENERAL] Index question regarding numeric operators

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 f

Re: [GENERAL] Index question

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 crea

[GENERAL] Index question

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 colu

Re: [GENERAL] index question

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 aggre

[GENERAL] index question

I have heard that postgres will not use an index unless the field has a not null constraint on it.  Is that true?

RE: [GENERAL] Index question

7;users' 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 PRO

[GENERAL] Index question

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 on