Hi,
I want to select the last contact of person via mail.
My sample database is build with the following shell-commands
| createdb -U postgres test2
| psql -U postgres test2 < mail_db.sql
| mailtest.sh | psql -U postgres
I call to get the answer
| SELECT address, max(sent) from mail inner join
| tomail on (mail.id=tomail.mail) group by address;
The result is ok, but it's to slow.
The query plan, see below, tells that there two seq scans.
Howto transforms them into index scans?
postgres ignores simple indexes on column sent.
An Index on two tables is not possible (if I understand the manual
correctly).
Any other idea howto speed up?
Ciao
Michael
===================
test2=# explain analyze SELECT address, max(sent) from mail inner join
tomail on (mail.id=tomail.mail) group by address;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=36337.00..36964.32 rows=50186 width=20) (actual
time=3562.136..3610.238 rows=50000 loops=1)
-> Hash Join (cost=14191.00..33837.00 rows=500000 width=20) (actual time=1043.537..2856.933 rows=500000 loops=1)
Hash Cond: (tomail.mail = mail.id)
-> Seq Scan on tomail (cost=0.00..8396.00 rows=500000 width=20) (actual time=0.014..230.264 rows=500000 loops=1)
-> Hash (cost=7941.00..7941.00 rows=500000 width=8) (actual time=1042.996..1042.996 rows=500000 loops=1)
-> Seq Scan on mail (cost=0.00..7941.00 rows=500000 width=8) (actual time=0.018..362.101 rows=500000 loops=1)
Total runtime: 3629.449 ms
(7 rows)
CREATE TABLE mail (
id serial primary key,
sent date,
subject character varying(256)
);
CREATE TABLE tomail (
id serial primary key,
mail integer references mail,
address character varying(256)
);
Attachment:
mailtest.sh
Description: Bourne shell script
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
