PostgreSQL query extremely slow

Question

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 !

"There is no indexes for now" and "in a 100K loop", sound like 2 major problems that explain your performance issues. But why don't you create a single query that selects all records that you need in just one blow? Use EXPLAIN ANALYZE to get the query plan and execution time, you can post it at explain.depesz.com — Frank Heikens
– Frank Heikens, Commented Feb 18, 2015 at 9:46
Why don't you create an index to improve the performance? CREATE INDEX TEMP_IDX_1 ON TEMP (ID, CAMPAIGN, DATE); — Christian
– Christian, Commented Feb 18, 2015 at 19:16
@Christian: An index might improve this query a lot, but if you execute it 100000 times, it's still very slow. The index is just a minor part of the performance optimization. — Frank Heikens
– Frank Heikens, Commented Feb 19, 2015 at 9:42

Clodoaldo Neto · Accepted Answer · 2015-02-18 10:00:15Z

2

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.

answered Feb 18, 2015 at 10:00

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Frank Heikens Over a year ago

This assumption looks already much better! Just one sequential read instead of 100k sequential reads.... Some indexes might improve performance, but it all depends!

Adeline Mani-Rajan Over a year ago

I am going to try this query and let you know if it is slow or not for responding

Collectives™ on Stack Overflow

PostgreSQL query extremely slow

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related