Re: index problem - Mailing list pgsql-sql

I forget:
select version();                               version
--------------------------------------------------------------------- PostgreSQL 7.1.3 on i386-unknown-freebsd4.3,
compiledby 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    ]