Received: from mail1.mclink.it (net128-007.mclink.it [195.110.128.7]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA12949 for ; Thu, 23 Sep 1999 07:22:51 -0400 (EDT) (envelope-from teknosoft@mclink.it) Received: from mclink.it (net133-199.mclink.it [195.110.133.199]) by mail1.mclink.it (8.9.1/8.9.0) with SMTP id NAA03671 for pgsql-sql@postgresql.org; Thu, 23 Sep 1999 13:22:36 +0200 (CEST) Date: Thu, 23 Sep 1999 13:22:36 +0200 (CEST) Message-Id: <199909231122.NAA03671@mail1.mclink.it> From: Roberto Fichera Reply-To: Roberto Fichera To: pgsql-sql@postgresql.org X-Mailer: Xmeo4 [ on Windows 95 4.0 (Pentium) ] Subject: Query performances 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)