Re: Postmaster processes taking all the CPU - Mailing list pgsql-general
| From | MC Moisei |
|---|---|
| Subject | Re: Postmaster processes taking all the CPU |
| Date | |
| Msg-id | BAY103-W1376C98063639E8FA8C033D51E0@phx.gbl Whole thread Raw |
| In response to | Postmaster processes taking all the CPU (MC Moisei <mcmoisei@hotmail.com>) |
| List | pgsql-general |
Thanks for replying!
I will try to upgrade to the latest on my prod box. Any easy way to do that ? Can I have two releases in the same time 7.4.7 and 8.2.3 ? This is a phased approach so it may take a little while to do it and I want to have 7.4.7 till I'd be able to switch to the 8.2.3( on a diff. port number)
On the speedup of the queries I doubt it's a developoer thing since on my dev box (8.1) the same queries are way faster.
On the lucene thing I do let the Lucene provide the best matches and I take those ids and feed the my query with...
select * from entity where id is in (:ids)
So if the entity scores more in Lucene search it will show higher, works pretty well. I can limit it to less first 100 result. I only display 5 at the time and I use a paginated approach, since you asked me.
Thanks again,
MC
I will try to upgrade to the latest on my prod box. Any easy way to do that ? Can I have two releases in the same time 7.4.7 and 8.2.3 ? This is a phased approach so it may take a little while to do it and I want to have 7.4.7 till I'd be able to switch to the 8.2.3( on a diff. port number)
On the speedup of the queries I doubt it's a developoer thing since on my dev box (8.1) the same queries are way faster.
On the lucene thing I do let the Lucene provide the best matches and I take those ids and feed the my query with...
select * from entity where id is in (:ids)
So if the entity scores more in Lucene search it will show higher, works pretty well. I can limit it to less first 100 result. I only display 5 at the time and I use a paginated approach, since you asked me.
Thanks again,
MC
> Date: Fri, 15 Jun 2007 20:03:08 +0200
> To: mcmoisei@hotmail.com; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Postmaster processes taking all the CPU
> From: lists@peufeu.com
>
>
> > I promised that I will get back to the group with the reason. Well, of
> > course was a query :). I do use a search engine file system
> > based(lucene) that will take any desired entity saved into the database
> > and find the primary keys and then do a select * from entity where id is
> > in (:ids)If I get too many matches(3000-4000)... that will delay my
> > postmaster and that postmaster associated with the query would take
> > 10-15 minutes to process that query. So, now I limit that to 500,
> > anything bigger than that will ask user to refine the query.However this
> > whole investigation made me observe some things. On my server box 7.4.7
> > I have some queries that are executing pretty slow 1.2-1.5secs it's a
> > lot for a query that goes thru 5000 records. On my local environment 8.1
> > the same queries on similar table size executes much faster like
> > 200-400ms. Do you know if this is a known issue or my dev box is better
> > than my server box?
>
> PG developers are not Microsoft, hence, every version of PG gets meaner
> and faster instead of slower and more bloated.
> The speedup you see might simply be due to developer competence...
>
> Anyway, if you use Lucene for full text search (I didn't try it, I tried
> Xapian which seems similar) :
>
> Users don't care about searches returning 5000 results ; they usually
> only look at the first pages. It's the sorting that is important, and this
> should be done by Lucene itself (sorting on phrase relevance, for
> instance, is a search engine job). So, you are right in limiting the
> results to 500, it could even be lower. The role of the search engine is
> to sort the good results at the top of the list so users are happy.
>
> Do you really display 500 results ? If you paginate the results, the
> users will probably never go past page3. Fetching a lot of results is
> therefore a waste of database power : just fetch the ids from Lucene and
> grab the results from Postgres, but only for the page you are going to
> display.
>
> If you use Postgres to do the sorting (example : lucene matches the
> records and you want to order them by a field which is not stored in
> Lucene but in Postgres) obviously this advice does not apply.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
pgsql-general by date: