From: Szabo Zoltan <col@mportal.hu>
To: pgsql-sql@postgresql.org
Subject: index problem
Date: Mon, 15 Oct 2001 21:41:10 +0200
Message-ID: <3BCB3BD6.70500@mportal.hu> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: col@mportal.hu
Subject: Re: index problem
In-Reply-To: <3BCB3BD6.70500@mportal.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox