Re: [PERFORM] PostgreSQL strange query plan for my query

On 11/16/2012 17:35, David Popiashvili wrote: Thanks Craig. Yes I already tried it but it didn't work. I don't see any solution other than fixing this bug. Take a look http://www.postgresql.org/search/?m=1&q=LIMIT&l=8&d=365&s=r. There are too many bug reports about LIMIT slowing down queries. L

Re: [PERFORM] PostgreSQL strange query plan for my query

Thanks Craig. Yes I already tried it but it didn't work. I don't see any solution other than fixing this bug. Take a look http://www.postgresql.org/search/?m=1&q=LIMIT&l=8&d=365&s=r. There are too many bug reports about LIMIT slowing down queries. Let's hope it will be fixed someday :) Date: F

Re: [PERFORM] PostgreSQL strange query plan for my query

On Fri, Nov 16, 2012 at 3:40 AM, David Popiashvili wrote: > I have database with few hundred millions of rows. I'm running the > following query: > > select * from "Payments" as pinner join "PaymentOrders" as poon po."Id" = > p."PaymentOrderId"inner join "Users" as uOn u."Id" = po."UserId"INNER J

Re: [PERFORM] intercepting where clause on a view or other performance tweak

Russell Keane writes: > Sorry, I should've added that in the original description. > I have an index on search_key and it's never used. Did you pay attention to the point about the nondefault operator class? If the LIKE pattern is left-anchored and as selective as your example implies, the planne

Re: [PERFORM] intercepting where clause on a view or other performance tweak

I should've also mentioned that we're using PG 9.0. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Russell Keane Sent: 16 November 2012 15:18 To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] interce

Re: [PERFORM] intercepting where clause on a view or other performance tweak

Sorry, I should've added that in the original description. I have an index on search_key and it's never used. If it makes any difference, the table is about 9MB and the index on that field alone is 3MB. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 16 November 201

Re: [PERFORM] intercepting where clause on a view or other performance tweak

Russell Keane writes: > Running the following query takes 56+ ms as it does a seq scan of the whole > table: > SELECT CODE FROM stuff >WHERE SEARCH_KEY LIKE 'AA%' Why don't you create an index on search_key, and forget all these other machinations? (If your locale isn't C you'll need to

[PERFORM] intercepting where clause on a view or other performance tweak

Hi, I have a table which contains generated static data (defined below) where the search_key field contains varying length strings. There are 122,000 rows in the table When the data is created the search_key field is ordered alphanumerically and assigned a unique order_key value starting at 1.

Re: [PERFORM] PostgreSQL strange query plan for my query

On 11/16/2012 14:04, David Popiashvili wrote: All right, after some discussion on StackOverflow , we found out that incorrect query plan is generated due to the fact that there is a LIMIT keyword in

Re: [PERFORM] PostgreSQL strange query plan for my query

All right, after some discussion on StackOverflow, we found out that incorrect query plan is generated due to the fact that there is a LIMIT keyword in the query. I guess Postgresql expects to find appropriate rows faster and that's why it generates a seq scan on the table. If I remove LIMIT 100

Re: [PERFORM] PostgreSQL strange query plan for my query

David Popiashvili wrote: > I have database with few hundred millions of rows. I'm running the following query: > > select * from "Payments" as p > inner join "PaymentOrders" as po > on po."Id" = p."PaymentOrderId" > inner join "Users" as u > On u."Id" = po."UserId" > INNER JOIN "Roles" as r > on u

[PERFORM] PostgreSQL strange query plan for my query

I have database with few hundred millions of rows. I'm running the following query: select * from "Payments" as p inner join "PaymentOrders" as po on po."Id" = p."PaymentOrderId" inner join "Users" as u On u."Id" = po."UserId" INNER JOIN "Roles" as r on u."RoleId" = r."Id" Where r."Name" = 'Moses