pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roberto Fichera <mc3641@mclink.it>
To: pgsql-sql@postgresql.org
Subject: Query performances !?
Date: Thu, 23 Sep 1999 13:11:14 +0200 (CEST)
Message-ID: <199909231111.NAA29784@mail1.mclink.it> (raw)


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)







view thread (2+ messages)  latest in thread

Message-ID: <199909231111.NAA29784@mail1.mclink.it>
Permalink:  ../199909231111.NAA29784@mail1.mclink.it/
Also on:    postgresql.org/message-id/199909231111.NAA29784@mail1.mclink.it

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mc3641@mclink.it
  Subject: Re: Query performances !?
  In-Reply-To: <199909231111.NAA29784@mail1.mclink.it>
  References: <199909231111.NAA29784@mail1.mclink.it>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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