pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query performances !?
2+ messages / 2 participants
[nested] [flat]

* Query performances !?
@ 1999-09-23 11:11 Roberto Fichera <mc3641@mclink.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Roberto Fichera @ 1999-09-23 11:11 UTC (permalink / raw)
  To: pgsql-sql


Hi all,

I'm new to PostgreSQL and I working on some tests. Currently I've loaded a
"moviment" table with ~600K rows (table and index descriptions are below).
I've compiled the 6.5.2 version on SCO Openserver 5.0.4, CPU is P200MMX,
RAM 64Mb, controller SCSI Tekram DC390WUF, 2 x HDD SCSI II Wide 4Gb.

My question is simple, on query below I notice that wasn't used any index.
Only sequential scan. Why ? I expect that the PostgreSQL use at least the
"moviment_index_10" index. And finally, why the costs for the 2 query are
the same ?

Thanks in advance.

Roberto Fichera,
TeknoSOFT.

+-----------------------------------------------------------------------------+
explain select mvm_cdep       as "Deposito",
       sum(mvm_qtmv)          as "Numero Pezzi",
       sum(mvm_qtmv*mvm_prez) as "Totale"
  from moviment
  where mvm_dreg between '1999/07/01'::date and '1999/07/31'::date and
        mvm_caus = '006' and
        mvm_cdep > 1 and mvm_annc = 1999
  group by mvm_cdep;
NOTICE:  QUERY PLAN:

Aggregate  (cost=42591.91 rows=68 width=12)
  ->  Group  (cost=42591.91 rows=68 width=12)
        ->  Sort  (cost=42591.91 rows=68 width=12)
              ->  Seq Scan on moviment  (cost=42591.91 rows=68 width=12)
+-----------------------------------------------------------------------------+
explain select mvm_cdep            as "Deposito",
       date_part('month',mvm_dreg) as "Mese",
       sum(mvm_qtmv)               as "Numero Pezzi",
       sum(mvm_qtmv*mvm_prez)      as "Totale"
  from moviment
  where mvm_dreg between '1999/05/01'::date and '1999/08/30'::date and
        mvm_caus = '006' and
        mvm_cdep > 1 and mvm_annc = 1999
  group by mvm_cdep,date_part('month',mvm_dreg);
NOTICE:  QUERY PLAN:

Aggregate  (cost=42591.91 rows=68 width=12)
  ->  Group  (cost=42591.91 rows=68 width=12)
        ->  Sort  (cost=42591.91 rows=68 width=12)
              ->  Seq Scan on moviment  (cost=42591.91 rows=68 width=12)
+-----------------------------------------------------------------------------+


Table    = moviment
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_nupr                         | float4                           |     4 |
| nri_moviment                     | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
| mvm_seri                         | char()                           |     2 |
| mvm_ndoc                         | float4                           |     4 |
| mvm_ccfd                         | float4                           |     4 |
| mvm_dreg                         | date                             |     4 |
| mvm_tidc                         | char()                           |     1 |
| mvm_cdep                         | float4                           |     4 |
| mvm_cage                         | char()                           |     3 |
| mvm_cagz                         | char()                           |     3 |
| mvm_cndp                         | float4                           |     4 |
| mvm_cncf                         | float4                           |     4 |
| mvm_caus                         | char()                           |     4 |
| mvm_zona                         | char()                           |     2 |
| mvm_szon                         | char()                           |     3 |
| mvm_fbol                         | char()                           |     1 |
| mvm_cart                         | char()                           |    15 |
| mvm_comm                         | char()                           |    15 |
| mvm_qtmv                         | float4                           |     4 |
| mvm_prez                         | float4                           |     4 |
| mvm_sco1                         | float4                           |     4 |
| mvm_sco2                         | float4                           |     4 |
| mvm_sco3                         | float4                           |     4 |
| mvm_sco4                         | float4                           |     4 |
| mvm_sco5                         | float4                           |     4 |
| mvm_impe                         | char()                           |     1 |
| mvm_stts                         | char()                           |     1 |
| mvm_fili                         | char()                           |     5 |
| mvm_rife                         | char()                           |    13 |
| mvm_caur                         | char()                           |     4 |
| mvm_filc                         | char()                           |    15 |
| mvm_sco6                         | float4                           |     4 |
| mvm_sco7                         | float4                           |     4 |
| mvm_sco8                         | float4                           |     4 |
| mvm_pa17                         | char()                           |     1 |
| mvm_pa18                         | char()                           |     1 |
| mvm_cate                         | char()                           |     4 |
| mvm_fill                         | char()                           |    21 |
| mvm_stat                         | char()                           |     1 |
| mvm_ftrs                         | char()                           |     1 |
| mvm_datr                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  moviment_index_0
          moviment_index_1
          moviment_index_10
          moviment_index_2
          moviment_index_3

Table    = moviment_index_0
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_nupr                         | float4                           |     4 |
| nri_moviment                     | float4                           |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
| mvm_seri                         | char()                           |     2 |
| mvm_ndoc                         | float4                           |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_10
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cdep                         | float4                           |     4 |
| mvm_dreg                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_2
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cncf                         | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_3
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cdep                         | float4                           |     4 |
| mvm_cart                         | char()                           |    15 |
| mvm_dreg                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+




Xmeo 4.b9 on Windows 95 4.0 (Pentium)







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Query performances
@ 1999-09-23 11:22 Roberto Fichera <teknosoft@mclink.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Roberto Fichera @ 1999-09-23 11:22 UTC (permalink / raw)
  To: pgsql-sql


Hi all,

I'm new to PostgreSQL and I working on some tests. Currently I've loaded a
"moviment" table with ~600K rows (table and index descriptions are below).
I've compiled the 6.5.2 version on SCO Openserver 5.0.4, CPU is P200MMX,
RAM 64Mb, controller SCSI Tekram DC390WUF, 2 x HDD SCSI II Wide 4Gb.

My question is simple, on query below I notice that wasn't used any index.
Only sequential scan. Why ? I expect that the PostgreSQL use at least the
"moviment_index_10" index. And finally, why the costs for the 2 query are
the same ?

Thanks in advance.

Roberto Fichera,
TeknoSOFT.

+-----------------------------------------------------------------------------+
explain select mvm_cdep       as "Deposito",
       sum(mvm_qtmv)          as "Numero Pezzi",
       sum(mvm_qtmv*mvm_prez) as "Totale"
  from moviment
  where mvm_dreg between '1999/07/01'::date and '1999/07/31'::date and
        mvm_caus = '006' and
        mvm_cdep > 1 and mvm_annc = 1999
  group by mvm_cdep;
NOTICE:  QUERY PLAN:

Aggregate  (cost=42591.91 rows=68 width=12)
  ->  Group  (cost=42591.91 rows=68 width=12)
        ->  Sort  (cost=42591.91 rows=68 width=12)
              ->  Seq Scan on moviment  (cost=42591.91 rows=68 width=12)
+-----------------------------------------------------------------------------+
explain select mvm_cdep            as "Deposito",
       date_part('month',mvm_dreg) as "Mese",
       sum(mvm_qtmv)               as "Numero Pezzi",
       sum(mvm_qtmv*mvm_prez)      as "Totale"
  from moviment
  where mvm_dreg between '1999/05/01'::date and '1999/08/30'::date and
        mvm_caus = '006' and
        mvm_cdep > 1 and mvm_annc = 1999
  group by mvm_cdep,date_part('month',mvm_dreg);
NOTICE:  QUERY PLAN:

Aggregate  (cost=42591.91 rows=68 width=12)
  ->  Group  (cost=42591.91 rows=68 width=12)
        ->  Sort  (cost=42591.91 rows=68 width=12)
              ->  Seq Scan on moviment  (cost=42591.91 rows=68 width=12)
+-----------------------------------------------------------------------------+


Table    = moviment
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_nupr                         | float4                           |     4 |
| nri_moviment                     | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
| mvm_seri                         | char()                           |     2 |
| mvm_ndoc                         | float4                           |     4 |
| mvm_ccfd                         | float4                           |     4 |
| mvm_dreg                         | date                             |     4 |
| mvm_tidc                         | char()                           |     1 |
| mvm_cdep                         | float4                           |     4 |
| mvm_cage                         | char()                           |     3 |
| mvm_cagz                         | char()                           |     3 |
| mvm_cndp                         | float4                           |     4 |
| mvm_cncf                         | float4                           |     4 |
| mvm_caus                         | char()                           |     4 |
| mvm_zona                         | char()                           |     2 |
| mvm_szon                         | char()                           |     3 |
| mvm_fbol                         | char()                           |     1 |
| mvm_cart                         | char()                           |    15 |
| mvm_comm                         | char()                           |    15 |
| mvm_qtmv                         | float4                           |     4 |
| mvm_prez                         | float4                           |     4 |
| mvm_sco1                         | float4                           |     4 |
| mvm_sco2                         | float4                           |     4 |
| mvm_sco3                         | float4                           |     4 |
| mvm_sco4                         | float4                           |     4 |
| mvm_sco5                         | float4                           |     4 |
| mvm_impe                         | char()                           |     1 |
| mvm_stts                         | char()                           |     1 |
| mvm_fili                         | char()                           |     5 |
| mvm_rife                         | char()                           |    13 |
| mvm_caur                         | char()                           |     4 |
| mvm_filc                         | char()                           |    15 |
| mvm_sco6                         | float4                           |     4 |
| mvm_sco7                         | float4                           |     4 |
| mvm_sco8                         | float4                           |     4 |
| mvm_pa17                         | char()                           |     1 |
| mvm_pa18                         | char()                           |     1 |
| mvm_cate                         | char()                           |     4 |
| mvm_fill                         | char()                           |    21 |
| mvm_stat                         | char()                           |     1 |
| mvm_ftrs                         | char()                           |     1 |
| mvm_datr                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+
Indices:  moviment_index_0
          moviment_index_1
          moviment_index_10
          moviment_index_2
          moviment_index_3

Table    = moviment_index_0
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_nupr                         | float4                           |     4 |
| nri_moviment                     | float4                           |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
| mvm_seri                         | char()                           |     2 |
| mvm_ndoc                         | float4                           |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_10
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cdep                         | float4                           |     4 |
| mvm_dreg                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_2
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cncf                         | float4                           |     4 |
| mvm_ddoc                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+

Table    = moviment_index_3
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| mvm_annc                         | float4                           |     4 |
| mvm_cdep                         | float4                           |     4 |
| mvm_cart                         | char()                           |    15 |
| mvm_dreg                         | date                             |     4 |
+----------------------------------+----------------------------------+-------+

Xmeo 4.b9 on Windows 95 4.0 (Pentium)







^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-09-23 11:22 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-23 11:11 Query performances !? Roberto Fichera <mc3641@mclink.it>
1999-09-23 11:22 Query performances Roberto Fichera <teknosoft@mclink.it>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox