[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re[2]: readlocals SQL query




Thursday, January 25, 2007, 6:57:37 PM, Alex Howansky wrote:

> On Thu, 25 Jan 2007, Catalin Constantin wrote:

>> I noticed in the postgresql log that a CPU consuming query is ran
>> quite often.
>>
>> SELECT DISTINCT virtual_host FROM passwd WHERE enabled='1' AND (startdate IS
>> NULL OR startdate<=CURRENT_TIMESTAMP) AND (stopdate IS NULL OR
>> stopdate>=CURRENT_TIMESTAMP);
>>
>> We host around 5000 domains and running this query that often slows
>> the server quite a lot.
>>
>> Can somebody explain why this query is needed ?

> This gathers the list of domains that you are hosting, i.e., the domains that
> your server accepts mail for.
Yes, i got that, but why GATHER all the list when you just need to
check for one domain.

>> How can it be optimized ?

> How many user records in the table?
We have 5561 records. (select count for the same query)

> Can you post the EXPLAIN output for your database?
userdb=# explain SELECT DISTINCT virtual_host FROM passwd WHERE enabled='1' AND (startdate IS NULL OR startdate<=CURRENT_TIMESTAMP) AND (stopdate IS NULL OR stopdate>=CURRENT_TIMESTAMP);
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=43773.16..45163.47 rows=81 width=16)
   ->  Sort  (cost=43773.16..44468.32 rows=278061 width=16)
         Sort Key: virtual_host
         ->  Seq Scan on passwd  (cost=0.00..12659.44 rows=278061 width=16)
               Filter: (enabled AND ((startdate IS NULL) OR (startdate <= now())) AND ((stopdate IS NULL) OR (stopdate >= now())))
(5 rows)


Anyway, i saw in qmail-send.c that it checks if it can find the FILE
to read from the filesystem with the "locals". If it can't find the
file then the query is ran.

We already have the list of domains in the rcpthosts file so all i did
was a symlink from rcpthosts to locals.

Now the readlocals (sql version) is never reached so the CPU consuming
query is never ran anymore.

This helped.

Thanks,


-- 
Catalin Constantin
Bounce Software
http://www.bounce-software.com
http://www.cabanova.ro