From: Dave Steinberg <dave-dated-1101824919.46cd20@redterror.net>
To: pgsql-sql@postgresql.org
Subject: PG7.4.5: query not using index on date column
Date: Tue, 23 Nov 2004 09:28:16 -0500
Message-ID: <20041123092816.51d7aa5e.dave@redterror.net> (raw)
Hi Folks, I was hoping someone could help me to improve the performance of a query I've got that insists on doing a seq. scan on a large table. I'm trying to do some reporting based on my spam logs which I've partly digested and stored in a table. Here are the particulars:
The messages table:
Column | Type | Modifiers
-------------------+-----------------------+--------------
message_id | integer | not null default nextval('spamreport.messages_message_id_seq'::text)
received_date | date | not null
score | numeric | not null
user_threshold | numeric | not null
raw_email_address | character varying(64) | not null
processing_time | numeric | not null
size | integer | not null
fuzzed_address | character varying(64) | not null
domain | character varying(64) | not null
Indexes:
"messages_pkey" primary key, btree (message_id)
"domain_idx" btree ("domain")
"fuzzy_idx" btree (fuzzed_address)
"received_date_idx" btree (received_date)
And here's the primary query I run, along with explain analyze output:
>> explain analyze SELECT * FROM ( SELECT
domain,
count(*) as count,
max(score) as max_score,
avg(score) as average_score,
stddev(score) as stddev_score,
sum(CASE WHEN score > user_threshold THEN 1 ELSE 0 END) as spam_count,
avg(processing_time) as average_time,
avg(size) as average_size
FROM messages
WHERE received_date BETWEEN '2004-11-01' AND '2004-11-30'
GROUP BY domain ) AS aggs
ORDER BY count DESC;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=30303.51..30303.60 rows=35 width=226) (actual time=29869.716..29869.883 rows=69 loops=1)
Sort Key: count
-> Subquery Scan aggs (cost=30301.56..30302.61 rows=35 width=226) (actual time=29861.705..29869.240 rows=69 loops=1)
-> HashAggregate (cost=30301.56..30302.26 rows=35 width=54) (actual time=29861.681..29868.261 rows=69 loops=1)
-> Seq Scan on messages (cost=0.00..21573.04 rows=436426 width=54) (actual time=5.523..6304.657 rows=462931 loops=1)
Filter: ((received_date >= '2004-11-01'::date) AND (received_date <= '2004-11-30'::date))
Total runtime: 29870.437 ms
This database gets vacuumed nightly. Also, the query plan stays the same even if I restrict the received_date column down to a single day.
Thanks in advance,
--
Dave Steinberg
http://www.geekisp.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dave-dated-1101824919.46cd20@redterror.net
Subject: Re: PG7.4.5: query not using index on date column
In-Reply-To: <20041123092816.51d7aa5e.dave@redterror.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox