Обсуждение: EXPLAIN times

We have two different instances of Postgresql running on two different
Redhat boxes:

OS: DB1 - Red Hat Linux release 7.1sbe (Seawolf)
     DB2 - Red Hat Linux release 7.2 (Enigma)

The postgres version for each box is 7.2.1.

When we run a simple query:

SELECT    ord_contact_name.l_name
FROM    ord_contact_name
JOIN    ord_dv_job_num
USING    (ord_obj_guid)
WHERE    (ord_dv_job_num.dv_job_number ='1334298')

These are the EXPLAIN results on DB1:

NOTICE:  QUERY PLAN:

Merge Join  (cost=152183.00..271426.66 rows=9416137 width=105)
   ->  Sort  (cost=141295.60..141295.60 rows=613709 width=69)
         ->  Seq Scan on ord_contact_name  (cost=0.00..18568.09
rows=613709 width=69)
   ->  Sort  (cost=10887.40..10887.40 rows=3069 width=36)
         ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
ord_dv_job_num  (cost=0.00..10709.67 rows=3069 width=36)

And the EXPLAIN results on DB2:

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..9.91 rows=1 width=90)
   ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
ord_dv_job_num  (cost=0.00..3.89 rows=1 width=40)
   ->  Index Scan using ord_obj_guid_ord_contact_name_k on
ord_contact_name  (cost=0.00..6.01 rows=1 width=50)


Usually, this means that the indices are corrupt, but we have reindexed
many times. Does anyone know why DB1 is so much slower than DB2?

Memory is the same on both machines - over a Gig of RAM.
DB1 and DB2 both are SMP and DB1 uses hardware RAID. However, DB1 is
running on two different nodes (if one ethernet card shuts down, the
other can still be accessed).

Does anyone have any suggestions?

Thank you

Afra

Afra <aa4@cse.buffalo.edu> writes:
> The postgres version for each box is 7.2.1.

Upgrading to 7.2.3 would be a good idea (it won't effect this problem,
it's just a good idea in general).

> These are the EXPLAIN results on DB1:
>
> NOTICE:  QUERY PLAN:
>
> Merge Join  (cost=152183.00..271426.66 rows=9416137 width=105)
>    ->  Sort  (cost=141295.60..141295.60 rows=613709 width=69)
>          ->  Seq Scan on ord_contact_name  (cost=0.00..18568.09
> rows=613709 width=69)
>    ->  Sort  (cost=10887.40..10887.40 rows=3069 width=36)
>          ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
> ord_dv_job_num  (cost=0.00..10709.67 rows=3069 width=36)
>
> And the EXPLAIN results on DB2:
>
> NOTICE:  QUERY PLAN:
>
> Nested Loop  (cost=0.00..9.91 rows=1 width=90)
>    ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
> ord_dv_job_num  (cost=0.00..3.89 rows=1 width=40)
>    ->  Index Scan using ord_obj_guid_ord_contact_name_k on
> ord_contact_name  (cost=0.00..6.01 rows=1 width=50)

Have you run VACUUM ANALYZE on both databases recently? (if you
haven't, do so). The statistics for DB1 look bizarrely incorrect, so
I'd suspect that updating them will fix the problem.

Is PostgreSQL configured in the same way on both systems?

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Thank you for the response.

We run VACUUM ANALYZE every night, so we don't think this is a problem.
Postgres was also installed the same ways for both and we fine-tuned
some of the variables in the config files (regarding memory, for example).

Thank you.

Afra

Neil Conway wrote:

> Afra  writes:
>
> >The postgres version for each box is 7.2.1.
>
>
> Upgrading to 7.2.3 would be a good idea (it won't effect this problem,
> it's just a good idea in general).
>
>
> >These are the EXPLAIN results on DB1:
> >
> >NOTICE:  QUERY PLAN:
> >
> >Merge Join  (cost=152183.00..271426.66 rows=9416137 width=105)
> >   ->  Sort  (cost=141295.60..141295.60 rows=613709 width=69)
> >         ->  Seq Scan on ord_contact_name  (cost=0.00..18568.09
> >rows=613709 width=69)
> >   ->  Sort  (cost=10887.40..10887.40 rows=3069 width=36)
> >         ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
> >ord_dv_job_num  (cost=0.00..10709.67 rows=3069 width=36)
> >
> >And the EXPLAIN results on DB2:
> >
> >NOTICE:  QUERY PLAN:
> >
> >Nested Loop  (cost=0.00..9.91 rows=1 width=90)
> >   ->  Index Scan using dv_job_number_ord_dv_job_num_ke on
> >ord_dv_job_num  (cost=0.00..3.89 rows=1 width=40)
> >   ->  Index Scan using ord_obj_guid_ord_contact_name_k on
> >ord_contact_name  (cost=0.00..6.01 rows=1 width=50)
>
>
> Have you run VACUUM ANALYZE on both databases recently? (if you
> haven't, do so). The statistics for DB1 look bizarrely incorrect, so
> I'd suspect that updating them will fix the problem.
>
> Is PostgreSQL configured in the same way on both systems?
>
> Cheers,
>
> Neil
>