Query performances !? - Mailing list pgsql-sql
| From | Roberto Fichera |
|---|---|
| Subject | Query performances !? |
| Date | |
| Msg-id | 199909231111.NAA29784@mail1.mclink.it Whole thread Raw |
| List | 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=68width=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'::dateand '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=68width=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)