Hello Pat, Here is the link: https://gist.github.com/841520
With: - define_index block - sql query on conf - explain - explain analyse Thanks in advance On Feb 23, 11:51 pm, Pat Allan <[email protected]> wrote: > Hi Pedro > > Perhaps post the EXPLAIN and EXPLAIN ANALYSE outputs to a gist? To be honest, > given I've not used the PostgreSQL equivalents, I'm not sure how much sense > I'll be able to make out of it, but let's start with that. > > Also, can you add the SQL query to the gist as well? > > Cheers > > -- > Pat > > On 24/02/2011, at 6:07 AM, Pedro Cunha wrote: > > > > > > > > > Hey Pat, I'm using PostgresSQL, and command is the same. > > > I tried looking at the output and it kinda looks like chinese to me. > > From what I see, I can see no reference to indexes whatsoever. That > > can be a problem. According to Postgres docs I must do an ANALYZE > > <table_name> to make pg server aware of an index on that specific > > table. > > > After I ve done that and run the EXPLAIN and EXPLAIN ANALYSE I still > > can't see any reference to indexes. That might be a problem. > > > Do you want me to copy paste here the whole log or is there some way I > > can attach that file to this thread? (it's a bit lengthy) > > > Best regards > > > On Feb 23, 12:31 am, Pat Allan <[email protected]> wrote: > >> Hi Pedro > > >> Given you've got the database indexes, this definitely feels far too slow. > >> Are you using MySQL or PostgreSQL? > > >> If you're using MySQL, then I would recommend taking the generated > >> sql_query value from the restaurant_core_0 source in your sphinx > >> configuration file, remove the $start and $end parts of the WHERE clause, > >> and add EXPLAIN to the start of the query. Then, run this query in a MySQL > >> console: > > >> EXPLAIN SELECT ... > > >> This should give you some information on what database indexes are being > >> used for the query. > > >> I'm not sure what the equivalent of the EXPLAIN command for PostgreSQL is, > >> but I'd be surprised if such a thing didn't exist. > > >> Cheers > > >> -- > >> Pat > > >> On 23/02/2011, at 1:32 AM, Pedro Cunha wrote: > > >>> Hello there, > > >>> I'm having a weird slow indexing. Tried looking for similar issues on > >>> this group and couldn't find. > > >>> When I index an has_many through association, perfomance decays a lot. > >>> To help trace this issue I ll give as much info as I can. > > >>> So the association looks like this: > > >>> Restaurant (1 : N ) Menu Categories > >>> Menu Categories ( 1 : N ) Menu Items > > >>> Index on table menu_categories for restaurant_id (DB level) > >>> Index on table menu_items for menu_category_id (DB level) > > >>> define_index on restaurant > >>> ... > >>> indexes menu_categories.menu_items(:name), :as => :menu_item_name > >>> indexes menu_categories.menu_items(:subtitle), :as > >>> => :menu_item_subtitle > >>> indexes menu_categories.menu_items(:description), :as > >>> => :menu_item_description > >>> ... > >>> end > > >>> Without the indexes before, and with a lot of other stuff I get this > >>> perfomance: > > >>> indexing index 'restaurant_core'... > >>> collected 12 docs, 3.0 MB > >>> collected 122936 attr values > >>> sorted 0.2 Mvalues, 100.0% done > >>> sorted 2.2 Mhits, 100.0% done > >>> total 12 docs, 2973437 bytes > >>> total 16.741 sec, 177611 bytes/sec, 0.71 docs/sec > >>> distributed index 'restaurant' can not be directly indexed; skipping. > > >>> With the indexes > > >>> indexing index 'restaurant_core'... > >>> collected 12 docs, 15.9 MB > >>> collected 423920 attr values > >>> sorted 0.8 Mvalues, 100.0% done > >>> sorted 11.4 Mhits, 100.0% done > >>> total 12 docs, 15885288 bytes > >>> total 320.783 sec, 49520 bytes/sec, 0.03 docs/sec > >>> distributed index 'restaurant' can not be directly indexed; skipping. > > >>> table ids are pretty small so I think that issue is not causing > >>> problems.. still I tried adding the sql step config and performance > >>> stays the same > > >>> Properties on define_index are: > > >>> set_property :min_prefix_len => 3 > >>> set_property :stopwords => "#{RAILS_ROOT}/config/sphinx/ > >>> stoplist.txt" > >>> set_property :delta => ThinkingSphinx::Deltas::ResqueDelta > > >>> Tried commenting min_prefix_len and perfomance keeps the same.. > > >>> Running out of ideas :( > > >>> Best regards, > >>> Pedro > > >>> -- > >>> You received this message because you are subscribed to the Google Groups > >>> "Thinking Sphinx" group. > >>> To post to this group, send email to [email protected]. > >>> To unsubscribe from this group, send email to > >>> [email protected]. > >>> For more options, visit this group > >>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/thinking-sphinx?hl=en. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
