Search found 67 matches

by jmpoure
Wed Jan 09, 2008 6:59 pm
Forum: [3.0/Olympus] New features discussion
Topic: PostgreSQL Fulltext Search
Replies: 40
Views: 117602

Re: PostgreSQL Fulltext Search

Attached is a version for RC4, the author_search method has changed. As for the caching, it would be interesting to see how the caching performs compared against a huge board. Perhaps I should make this an option for fulltext_postgres? I haven't looked at that code too deeply, it is a possibility I...
by jmpoure
Wed Jan 09, 2008 1:19 pm
Forum: [3.0/Olympus] Discussion
Topic: PostgreSQL query optimisation HOWTO
Replies: 1
Views: 4674

PostgreSQL query optimisation HOWTO

Dear friends, Here is a description of my PostgreSQL benchmarking tools : :arrow: TIP 1 : Log all queries on a production server The overhead is not very important as PostgreSQL logging deamon is a seperate process. In /etc/postgresql/8.2/main/postgresql.conf, change values log_destination = 'stderr...
by jmpoure
Wed Jan 09, 2008 12:50 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

In my post, there are examples where using a LEFT JOIN gives 20% speedup on an INNER JOIN. The advantage may be better given context change, memory use, CPU time, etc .. You can look at this post which is the perfect example : http://area51.phpbb.com/phpBB/viewtopic.php?f=3&t=29260&start=40#p191366 ...
by jmpoure
Wed Jan 09, 2008 8:34 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

In most situations, including deep queries, LEFT JOINS are faster because they require less overhead and are hard-wired more quickly. When you run an INNER JOIN, the parser will ask itself LEFT, RIGHT, INNER ? Then it will process data and remove duplicates. You don't have this kind of overhead in L...
by jmpoure
Tue Jan 08, 2008 3:18 pm
Forum: [3.0/Olympus] Discussion
Topic: Board is slow
Replies: 1
Views: 1982

Re: Board is slow

No idea, normal operation again.
by jmpoure
Tue Jan 08, 2008 3:10 pm
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

Dear Acyd Burn, This is your choice, but you should add indexing and replace INNER JOINS with LEFT JOINS and rewrite some queries. Then why not create VIEWs on some queries. * Most databases support VIEWS, including MySQL. * DB2, Oracle and PostgreSQL (with PL) support materialised views. The advant...
by jmpoure
Tue Jan 08, 2008 12:28 pm
Forum: [3.0/Olympus] Discussion
Topic: Board is slow
Replies: 1
Views: 1982

Board is slow

Hello, I am opening this post to discuss pconnect strategies in PostgreSQL. Currently, 250 users are connected on my board. CPU usage is 5%, SWAP is not used and ... 2 postgres processes are currently querying the database (idle) : postgres 13610 1.5 1.0 408364 21532 ? Ss 13:27 0:00 postgres: phpbb ...
by jmpoure
Tue Jan 08, 2008 8:04 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

To summarise my opinion: * tsearch2 should come as a standard choice for users. When tsearch2 is present, use it. * INNER JOINS or JOINS should be replaced with LEFT JOINS whenever possible. * Some tables carry indexes on 2 or more columns. These kind of indexes cannot be used in JOINS. Therefore, i...
by jmpoure
Tue Jan 08, 2008 7:53 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

In this case, it looks like the query planner chose the same join order on the tables... There is a real difference: the first query does a simple JOIN (Hash Join (cost=548.12..697.53 rows=347 width=52) (actual time=8.597..10.044 rows=551 loops=1)), the second a LEFT JOIN (Hash Left Join (cost=548....
by jmpoure
Tue Jan 08, 2008 7:46 am
Forum: [3.0/Olympus] Discussion
Topic: SQL optimisation
Replies: 70
Views: 49593

Re: SQL optimisation

The differences in those query plans come down to differences in the sequential scan, which largely has nothing to do with the query itself... I do not agree. During an INNER JOIN, the parser will consider LEFT and RIGHT possibilities. On the converse, a LEFT JOIN is always faster, because the quer...