Index columns in postgres

I am in a holding pattern while client decides upon changes and so I am
working some things out internally so it seems to me that indexing
frequently searched table fields might be useful.

I am using postgres and via postgres, I have added an index to one of my
tables whose index is the same name as the column name. The ‘find’
screen I am using this to judge populates a number of ‘selection lists’
from a ‘valuelists’ table so it hits this table with 4 separate queries
to populate 4 separate selection lists. I can’t see any difference in
speed. Do I have to reference the index within my controller code?

ie…

@employment_type = Valuelist.find(:all,
 :conditions => ["list_name = 'Employment Type'"],
 :order => 'list_value')

@position = Valuelist.find(:all,
  :conditions => ["list_name = 'Position'"],
  :order => 'list_value')

‘list_name’ is now indexed but it still takes the same amount of time to
display this page which I am sure is the result of 4 separate ‘finds’ in
“valuelists” table (2 are shown above). Am I supposed to reference the
index in controller code above?

Craig

On 6/19/06, Craig W. [email protected] wrote:

The ‘find’
screen I am using this to judge populates a number of ‘selection lists’
from a ‘valuelists’ table so it hits this table with 4 separate queries
to populate 4 separate selection lists. I can’t see any difference in
speed. Do I have to reference the index within my controller code?

Don’t use the browser to profile your code. See if the indexes are
actually helping out. The first thing to do is remove the indexes
that you just added, so you can see what the speed is before the
index. Connect to your db via the command line and type ‘\timing’ to
turn timing on. Then run a few of the queries you’re talking about
(make them different so the results aren’t cached) and get the average
speed. Add you indexes, run similar queries (with different data,
again to avoid cached results) and see what the speedup is.

Pat

On Mon, 2006-06-19 at 10:35 -0600, Pat M. wrote:

index. Connect to your db via the command line and type ‘\timing’ to
turn timing on. Then run a few of the queries you’re talking about
(make them different so the results aren’t cached) and get the average
speed. Add you indexes, run similar queries (with different data,
again to avoid cached results) and see what the speedup is.


OK - I sense a lack of respect for my seat of the pants and clearly
unscientific methodology of testing :wink:

I turned on timing in postgres but as far as I can tell, nothing is
getting logged either to screen or to any log that I can find.

rails log/development.log pretty much shows the same returns with or
without but caching could be influencing it but I don’t think so since
it always seems to take between 10-12 seconds (with or without index)

Craig

On 6/19/06, Craig W. [email protected] wrote:

that you just added, so you can see what the speed is before the
index. Connect to your db via the command line and type ‘\timing’ to
turn timing on. Then run a few of the queries you’re talking about
(make them different so the results aren’t cached) and get the average
speed. Add you indexes, run similar queries (with different data,
again to avoid cached results) and see what the speedup is.


OK - I sense a lack of respect for my seat of the pants and clearly
unscientific methodology of testing :wink:

It’s not a lack of respect, I’m just pointing out that you have
absolutely no idea how long the queries are taking.

I turned on timing in postgres but as far as I can tell, nothing is
getting logged either to screen or to any log that I can find.

Run the queries from within psql, not rails.

rails log/development.log pretty much shows the same returns with or
without but caching could be influencing it but I don’t think so since
it always seems to take between 10-12 seconds (with or without index)

Well that’s why you check to see how long the db queries take, to
determine if the bottleneck is in your queries or somewhere else :slight_smile:

Pat

On Mon, 2006-06-19 at 11:32 -0600, Pat M. wrote:

actually helping out. The first thing to do is remove the indexes
It’s not a lack of respect, I’m just pointing out that you have
absolutely no idea how long the queries are taking.


yeah, I was smiling when I wrote that.

I turned on timing in postgres but as far as I can tell, nothing is
getting logged either to screen or to any log that I can find.

Run the queries from within psql, not rails.


explains why I wasn’t seeing anything from rails activity

rails log/development.log pretty much shows the same returns with or
without but caching could be influencing it but I don’t think so since
it always seems to take between 10-12 seconds (with or without index)

Well that’s why you check to see how long the db queries take, to
determine if the bottleneck is in your queries or somewhere else :slight_smile:


I think my issues are probably more clearly described by Ian’s reply

Craig

On Mon, 2006-06-19 at 17:42 +0000, Ian H. wrote:

speed. Do I have to reference the index within my controller code?
you can also use EXPLAIN to see how the database will go about getting
your data.


yeah - I actually am subscribed to pgsql-general

I think you hit the nail on the head…this table is relatively small
(perhaps 200 rows) - This is probably an area where production
environment caching would handle this very well but indexes aren’t
likely to help. I was going to start the thread in a caching vs.
indexing theory but I thought it would make the topic too diverse to get
much traction.

I haven’t really created any indexes yet in any of my tables but so far,
performance hasn’t been an issue with the exception of this one find
screen with simply populates a number of selection lists with separate
sql finds from my ‘valuelists’ table. Caching is clearly the better
answer here.

Thanks Pat, Ian

Craig

On 6/19/06, Craig W. [email protected] wrote:

Creating an index will not always result in faster query results. If
the table is small, or the index not selective enough, scanning the
table might be faster than using the index. Also, you will need to
make sure your table has been analyzed to make sure the statistics are
up to date.

I recommend subscribing to a pgsql-general or pgsql-performance if you
want to learn about it. The advice about timing your queries is good,
you can also use EXPLAIN to see how the database will go about getting
your data.

On Jun 19, 2006, at 11:04 AM, Craig W. wrote:

I think you hit the nail on the head…this table is relatively small
(perhaps 200 rows) - This is probably an area where production
environment caching would handle this very well but indexes aren’t
likely to help. I was going to start the thread in a caching vs.
indexing theory but I thought it would make the topic too diverse
to get
much traction.

With 200 rows, Postgres probably has the entire table in memory.

There’s NO WAY (except for extreme generalized system performance
issues, i.e. swap, failing disk drives, network connectivity, etc)
that 4 queries is taking anywhere near the majority of the 12 second
time you’re talking about.

It’s likely that the vast majority of what you’re seeing is development
environment delay in reloading the models, etc.

Working on controller caching at this point would be a big mistake,
IMHO.


– Tom M.

On Mon, 2006-06-19 at 12:16 -0700, Tom M. wrote:

difference in
With 200 rows, Postgres probably has the entire table in memory.
IMHO.


Not that I doubt you but I don’t have that lag on any other screen
including screens that populate 1 or 2 of the same selection lists.

I am still quite a ways from moving this section of code from
development to production (they are redefining their needs now that I
have done a bunch of work :wink:

You did make me check things out though and interestingly enough, on my
home development system, there is very little lag time on this screen
and I am running postgresql on my server where as on the clients system,
I am running the postgresql server (everything) on localhost so it may
be a swap thing…I’m gonna look at that.

Thanks

Craig

Craig

Pat M. wrote:

The first thing to do is remove the indexes
that you just added

I think indexes can be disabled for queries, rather than removing
them…

Joe

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs