Re: [PERFORM] Unexpected sequential scan on an indexed column

Thanks, Dave. Eddy On Mon, Nov 16, 2009 at 1:52 PM, Dave Crooke wrote: > With Postgres, you can transparently replace a regular select with a > function that takes the same types and returns a record iterator with the > same columns. The only change needed is the SQL used to invoke it, you won't

Re: [PERFORM] Unexpected sequential scan on an indexed column

With Postgres, you can transparently replace a regular select with a function that takes the same types and returns a record iterator with the same columns. The only change needed is the SQL used to invoke it, you won't need any logic changes in your app code (Java or whatever), e.g. *select .

Re: [PERFORM] Unexpected sequential scan on an indexed column

This is incredibly helpful, Kenneth. I didn't know about the SETOF syntax at all. This could help minimize the amount of refactoring I need to do. Thanks! Eddy On Mon, Nov 16, 2009 at 12:55 PM, Kenneth Marshall wrote: > On Mon, Nov 16, 2009 at 12:45:46PM -0800, Eddy Escardo-Raffo wrote: > > Yea

Re: [PERFORM] Unexpected sequential scan on an indexed column

On Mon, Nov 16, 2009 at 12:45:46PM -0800, Eddy Escardo-Raffo wrote: > Yeah this kind of thing would probably work. Doing this in java with > separate queries would be easy to code but require multiple round trips. > Doing it as a stored procedure would be nicer but I'd have to think a little > more

Re: [PERFORM] Unexpected sequential scan on an indexed column

Yeah this kind of thing would probably work. Doing this in java with separate queries would be easy to code but require multiple round trips. Doing it as a stored procedure would be nicer but I'd have to think a little more about how to refactor the java code around the query to make this happen. T

Re: [PERFORM] Unexpected sequential scan on an indexed column

Hi Eddy Perhaps a slightly naive suggestion have you considered converting the query to a small stored procedure ('function' in Postgres speak)? You can pull the location values, and then iterate over a query like this: select userid from users where location=:x which is more-or-less guaran

Re: [PERFORM] Unexpected sequential scan on an indexed column

OK, I think that after reading this doc (which I hadn't encountered before) about the optimizer, something clicked in my brain and I think I can answer my own question. I was basically thinking from my own perspective rather than from the que

Re: [PERFORM] Unexpected sequential scan on an indexed column

Eddy Escardo-Raffo writes: > For C, the planner estimated 10 thousand rows. For D, the planner estimated > 100 thousand rows, yet for E the planner estimated only 1 row, which is the > closest to reality. So, is there any way to specify a query that has a > SUB-SELECT that returns a small set of v

Re: [PERFORM] Unexpected sequential scan on an indexed column

Yeah, that was it. Thanks! I do have one more question at the bottom, though, if anyone has enough time to read through my analysis If I create the table as: CREATE TABLE users ( userid integer NOT NULL, location integer NOT NULL, CONSTRAINT pk_users PRIMARY KEY (userid) ) WITH ( OIDS=FALSE ); C

Re: [PERFORM] Unexpected sequential scan on an indexed column

Thanks, Tom. I had discarded the possibility of data type mismatch already, which was your first guess, but was wondering if the lopsided distribution of location values would lead the planner to make a decision that is good on average but bad for this particular query, as you point out in your sec

Re: [PERFORM] Unexpected sequential scan on an indexed column

Eddy Escardo-Raffo writes: > The table used in this query is called "users", and it has columns "userid" > (primary key) and "location". > The "location" column is indexed. > The users table has 1 million rows, and all rows have integer typed value > '-1' for "location" column, except for 2 rows

Re: [PERFORM] Unexpected sequential scan on an indexed column

Eddy Escardo-Raffo writes: > Do you guys have any idea why this is not working as I expect? Datatype issue maybe? When I try what seems to be the same case here I get the expected indexscan, so I'm thinking the problem is that the comparison isn't indexable, which is a possibility if the locatio

[PERFORM] Unexpected sequential scan on an indexed column

Hi, everyone. Between postres docs, forum posts, previous similar questions answered and random blogs, I've read as much as I could about why others have had similar problems in the past before turning to you guys for help, so I really hope this is not some completely obvious oversight on my part