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 White <craigwhite@azapple.com> 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 Maddox wrote:> On 6/19/06, Craig White <craigwhite@azapple.com> 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.---- OK - I sense a lack of respect for my seat of the pants and clearly unscientific methodology of testing ;-) 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 White <craigwhite@azapple.com> wrote:> On Mon, 2006-06-19 at 10:35 -0600, Pat Maddox wrote: > > On 6/19/06, Craig White <craigwhite@azapple.com> 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. > ---- > OK - I sense a lack of respect for my seat of the pants and clearly > unscientific methodology of testing ;-)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 :) Pat
On 6/19/06, Craig White <craigwhite@azapple.com> wrote:> 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? >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 Mon, 2006-06-19 at 11:32 -0600, Pat Maddox wrote:> On 6/19/06, Craig White <craigwhite@azapple.com> wrote: > > On Mon, 2006-06-19 at 10:35 -0600, Pat Maddox wrote: > > > On 6/19/06, Craig White <craigwhite@azapple.com> 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. > > ---- > > OK - I sense a lack of respect for my seat of the pants and clearly > > unscientific methodology of testing ;-) > > 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 :)---- I think my issues are probably more clearly described by Ian''s reply Craig
On Mon, 2006-06-19 at 17:42 +0000, Ian Harding wrote:> On 6/19/06, Craig White <craigwhite@azapple.com> wrote: > > 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? > > > > 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.---- 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 Jun 19, 2006, at 11:04 AM, Craig White wrote:>>> 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? > > 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 Mornini
On Mon, 2006-06-19 at 12:16 -0700, Tom Mornini wrote:> On Jun 19, 2006, at 11:04 AM, Craig White wrote: > > >>> 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? > > > > 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.---- 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 ;-) 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