Обсуждение: A query with performance problems.

Поиск
Список
Период
Сортировка

A query with performance problems.

От
"Fabio C. Bon"
Дата:
Hi !

I have a database on PostgreSQL 7.2.1 and I have performance's problems with
some queries.
I'm debbuging the query below:

Select count(*) from blcar
where manide = 3811 and blide = 58090 and bcalupcod = 'MVDUY' and bcalopcod
= 'LOCAL' and bcapag <> 'P';

From the command prompt of Psql:

                                                    QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------
 Aggregate  (cost=3.03..3.03 rows=1 width=0) (actual time=0.20..0.20 rows=1
loops=1)
   ->  Index Scan using iblsec on blcar  (cost=0.00..3.02 rows=1 width=0)
(actual time=0.19..0.19 rows=0 loops=1)
         Index Cond: ((manide = 3811) AND (blide = 58090))
         Filter: ((bcalupcod = 'MVDUY'::bpchar) AND (bcalopcod =
'REPRE'::bpchar) AND (bcapag <> 'P'::bpchar))
 Total runtime: 0.30 msec
(5 rows)

From a file with a SQL sentence. (I execute it this way: \i filename)


QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------
 Aggregate  (cost=8277.10..8277.10 rows=1 width=0) (actual
time=1273.98..1273.98 rows=1 loops=1)
   ->  Seq Scan on blcar  (cost=0.00..8277.09 rows=1 width=0) (actual
time=1273.96..1273.96 rows=0 loops=1)
         Filter: (((manide)::numeric = 3811::numeric) AND ((blide)::numeric
= 58090::numeric) AND (bcalupcod = 'MVDUY'::bpchar) AND (bcalopcod =
'REPRE'::bpchar) AND (bcapag <> 'P'::bpchar))
 Total runtime: 1274.08 msec
(4 rows)

The problem is how one understands this duality of execution plans for the
same sentence in two situations which are really the same.
It's a relevant matter, because I need to solve performance problems
involved with the execution of this sentence from a program, and due to the
execution time this query required (according to the logfile of database), I
understand that it is choosing the second plan, when it is more reasonable
to use the first plan.

Thanks