Re: index problem

From: Szabo Zoltan <col(at)mportal(dot)hu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: index problem
Date: 2001-10-16 15:57:16
Message-ID: 3BCC58DC.2040402@mportal.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 ]

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Haller Christoph 2001-10-16 16:45:27 Deleting obsolete values
Previous Message Bruce Momjian 2001-10-16 15:55:21 Re: VARCHAR vs TEXT