Re: 7.4 performance issue - Mailing list pgsql-general

Christophe Musielak wrote:
> Hi,
>
> We're working since 4 years on Postgres for a e-crm web based
> application that deals with emails.

A few immediate observations:

1. Try not to reply to an existing message when posting a new question,
it can mess up threaded views.
2. The performance list is probably the best place for this.

> Total running time Postges: 10 - 11s
> Total running time sql Server : < 1s
>
> We must admit that we're quite disappointed with the result and
> currently think there is something wrong in the way we're working with
> Postgres.

Hmm - it's not common to get that sort of difference.

> PS : Concerning the seq_scan shown in the explain plan, as there is
> indexes on the tables as show below, we think Postgres is choosing
> seq_scan versus index_scan to improve performance. We tried to force
> using index_scan with 'set enable_seqscan = false) but there is no
> improvement in the total duration of the query.

Which suggests PG is getting it right.

> explain SELECT t.*, substring(t.subject::text, 0, 30) AS subject,
> e.email,
> o.lastname AS "operator"
> FROM threads t JOIN emails e ON (e.id = t.client_email_id)
> JOIN operators o ON (o.id = t.operator_id)
> WHERE t.mailbox_id IN ( 2,3,5,20,21,13,22,23,24,25,26,19 )
> and t.desktop = 2
> ORDER BY t.date_last asc, t.id asc
>  LIMIT 16 OFFSET 0;

OK, so you're filtering on desktop,mailbox_id mostly. Could you post the
output of EXPLAIN ANALYSE rather than just EXPLAIN. That'll show us not
just what PG thinks should happen, but what actually did happen too.

 From the explain, the greatest cost seems to be the join on threads to
email, where it's using the index scan on emails_pkey.

>
> QUERY PLAN
> ------------------------------------------------------------------
>
> Limit  (cost=93539.02..93539.06 rows=16 width=619)
>    ->  Sort  (cost=93539.02..93745.69 rows=82669 width=619)
>          Sort Key: t.date_last, t.id
>          ->  Hash Join  (cost=26186.03..73789.00 rows=82669 width=619)
>                Hash Cond: ("outer".operator_id = "inner".id)
>                ->  Merge Join  (cost=26183.07..69487.26 rows=82668
> width=609)
>                      Merge Cond: ("outer".id = "inner".client_email_id)
>                      ->  Index Scan using emails_pkey on emails e
> (cost=0.00..37782.57 rows=654511 width=30)
>                      ->  Sort  (cost=26183.07..26389.74 rows=82667
> width=583)
>                            Sort Key: t.client_email_id
>                            ->  Seq Scan on threads t
> (cost=0.00..19431.23
> rows=82667 width=583)
>                                  Filter: (((mailbox_id = 2) OR
> (mailbox_id
> = 3) OR (mailbox_id = 5) OR (mailbox_id = 20) OR (mailbox_id = 21) OR
> (mailbox_id = 13) OR                (mailbox_id = 22) OR (mailbox_id =
> 23)
> OR (mailbox_id = 24) OR (mailbox_id = 25) OR (mailbox_id = 26)
>             OR (mailbox_id = 19)) AND (desktop = 2))
>                ->  Hash  (cost=2.85..2.85 rows=44 width=14)
>                      ->  Seq Scan on operators o  (cost=0.00..2.85
> rows=44
> width=14)
>
>
> ------------------------------------------------------------------
>
> Indexes on threads :
>
> threads_pkey PRIMARY KEY (id)
> CREATE INDEX threads_date_last_idx   ON threads  (date_last);
> CREATE INDEX threads_desktop_idx  ON threads  (desktop);
> CREATE INDEX threads_operator_id_idx  ON threads  (operator_id);
> CREATE UNIQUE INDEX threads_pri_dlast_id_idx ON threads (priority,
> date_last, id);

None of these indexes are going to be much use here - you're mostly
filtering on mailbox_id. Unless "desktop" is very selective then a
sequential-scan seems sensible.

I'd recommend running an EXPLAIN ANALYSE and posting that to the
performance list.

You might want to include your postgresql.conf settings along with some
more details on your hardware. b
--
   Richard Huxton
   Archonet Ltd