Thread: index on two tables or Howto speedup max/aggregate-function

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)

Hi,

CREATE INDEX mail_id_sent_idx ON mail(id,sent)

should do the trick? Of course you can only replace one of the two
scans by an index scan since there are no other conditions...

Jacques.

At 09:59 13/10/2009, Michael Schwipps wrote:
>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)
>
>
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance