Received: from news.tht.net (news.hub.org [216.126.91.242])
	by postgresql.org (8.11.3/8.11.4) with ESMTP id f9GG5HP86836
	for <pgsql-sql@postgresql.org>; Tue, 16 Oct 2001 12:05:17 -0400 (EDT)
	(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id
 f9GFpv187205
	for pgsql-sql@postgresql.org; Tue, 16 Oct 2001 11:51:57 -0400 (EDT)
	(envelope-from news)
From: Szabo Zoltan <col@mportal.hu>
X-Newsgroups: comp.databases.postgresql.sql
Subject: Re: index problem
Date: Tue, 16 Oct 2001 17:57:16 +0200
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 98
Message-ID: <3BCC58DC.2040402@mportal.hu>
References: <3BCB3BD6.70500@mportal.hu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: scrappy@hub.org
User-Agent: Mozilla/5.0 (Windows; U; Win95; en-US;
	rv:0.9.2) Gecko/20010726 Netscape6/6.1
X-Accept-Language: en-us
To: pgsql-sql@postgresql.org
X-Archive-Number: 200110/228
X-Sequence-Number: 5183

I forget:
select version();
                                version
---------------------------------------------------------------------
  PostgreSQL 7.1.3 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3

It seems that there are index using problems in 7.1.3 ?
(checkin same problem in comp.databases.postgresql.bugs msg from Orion)

thx
CoL

Szabo Zoltan wrote:

> Hi,
> 
> I have that:
> 
> 1)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121;
> NOTICE:  QUERY PLAN:
> 
> Group  (cost=0.00..29970.34 rows=921 width=4)
>   ->  Index Scan using progdgyxy_idx2 on prog_dgy_xy 
> (cost=0.00..29947.32 rows=9210 width=4)
> 
> than:
> 2)
> db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121;
> NOTICE:  QUERY PLAN:
> 
> Group  (cost=66927.88..67695.39 rows=30700 width=4)
>   ->  Sort  (cost=66927.88..66927.88 rows=307004 width=4)
>         ->  Seq Scan on prog_dgy_xy  (cost=0.00..32447.66 rows=307004 
> width=4)
> 
> I making some banchmarks on: oracle vs postgres vs mysql. And this is 
> breaking me now;) Mysql and oracle width same table and index use that 
> index on pxygy_pid;
> I had vacuum before.
> 
> Time with mysql:
> 
> bash-2.04$ time echo " select count(*) from PROG_DGY_XY where 
> pxygy_pid>12121;" | mysql -uuser -ppasswd db
> count(*)
> 484984
> 
> real    0m13.761s
> user    0m0.008s
> sys     0m0.019s
> 
> Time with postgres:
> bash-2.04$ time echo "select count(*) from PROG_DGY_XY where 
> pxygy_pid>12121 " | psql -Uuser db
>  count
> --------
>  484984
> (1 row)
> 
> 
> real    0m22.480s
> user    0m0.011s
> sys     0m0.021s
> 
> And this is just a little part of another selects joining tables, but 
> because this index is not used, selecting from 2 tables (which has 
> indexes, and keys on joining collumns) takes extrem time for postgres: 
> 2m14.978s while for mysql it takes: 0m0.578s !!!
> 
> this select is: select distinct 
> PROG_ID,PROG_FTYPE,PROG_FCASTHOUR,PROG_DATE from PROG_DATA, PROG_DGY_XY 
>  where prog_id=pxygy_pid  order by prog_date,prog_ftype,prog_fcasthour
> 
> indexes:
> PROG_DATA:
> create index prod_data_idx1 on prog_data 
> (prog_date,prog_ftype,prog_fcasthour);
> prog_id is primary key
> 
> PROG_DGY_XY:
> create unique index progdgyxy_idx1 on PROG_DGY_XY 
> (PXYGY_PID,PXYGY_X,PXYGY_Y);
> create index progdgyxy_idx2 on PROG_DGY_XY (PXYGY_PID);
> 
> 
> Thx
> CoL
> 


-- 
[         Szabo Zoltan              ]
[       software fejleszto          ]
[    econet.hu Informatikai Rt.     ]
[ 1117 Budapest, Hauszmann A. u. 3. ]
[   tel.: 371 2100 fax: 371 2101    ]