PostgreSQL查询速度极慢

问题描述：

I have a table in a PostgreSQL database : temp with 4 columns

(
    id int, 
    campaign character varying, 
    sender character varying, 
    date_s date
)

with around 9 millions records already. There is no indexes for now.

The problem is when I am trying to do a :

SELECT COUNT(*) 
FROM temp 
WHERE 
    id = $idmail and 
    campaign = '$variable_campaign' AND 
    date > '$date_c' "

in a 100K loop.

The query is not responding. (I have put a unlimited set_time_limit in PHP otherwise I'll get a 500 error under 5 minutes)

Actually the purpose of all this queries is to get a list of mails to which the concerned campaign was not sent in the current week.

Have you got any ideas please because I am really don't know how to do !

I can do a kind of temporary files if queries cannot be executed but I prefer deal with databases, it's cleaner !

我在PostgreSQL数据库中有一个表：temp code> 4列 p>

 （
 id int，
广告系列字符不同，
发件人字符不同，
 date_s date 
）
  code>  pre> 
 
  
 
 问题是当我尝试执行以下操作时： p> 
 
 
  SELECT COUNT（*）
FROM temp 
WHERE 
 id =  $ idmail和
 campaign ='$ variable_campaign'AND 
 date＆gt; 在100K循环中'$ date_c'“
  code>  pre> 
 
 。 p> 
 
 
查询没有响应。（我放了一个无限的 在PHP中使用set_time_limit，否则我将在5分钟内收到500错误。） p> 
 
 
实际上，所有这些查询的目的是获取相关广告系列未在其中发送的邮件列表。 当前一周。 p> 
 
 
您有任何想法，因为我真的不知道该怎么办！ p> 
 
 
我可以做一种临时的 文件，如果查询无法执行，但我更喜欢处理数据库，它更干净！ p> 
  div>

答

Supposing there is a mails table with the id primary key:

select id    
from
    temp t
    right join
    mails m using(id)
where 
    t.campaign = '$variable_campaign' and
    t.date > '$date_c' and
    t.id is null

It will return all not sent mail ids.

PostgreSQL查询速度极慢

相关推荐