Re: [SQL] index problem

[Szabo Zoltan]

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
> 
> real0m13.761s
> user0m0.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)
> 
> 
> real0m22.480s
> user0m0.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]


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] index problem

[Szabo Zoltan]

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

real0m13.761s
user0m0.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)


real0m22.480s
user0m0.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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] to_char()??

[Szabo Zoltan]

Try: ::text

CoL

guard wrote:

> thanks
> 
> I have run "select substr('hi there', 3, 5)::varchar(5) as xx;"
> but get error message
> Error: ERROR:  parser: parse error at or near ":"
> 
> --
> 
> "Lee Harr" <[EMAIL PROTECTED]> ¼¶¼g©ó¶l¥ó·s»D
> :9qd0j0$1gc3$[EMAIL PROTECTED]
> 
>>>how to
>>>select substr('hi there',3,5) as xx   -->>  xx change char type
>>>
>>>
>>How about:
>>
>>select substr('hi there', 3, 5)::varchar(5) as xx;
>>
>>or is this not what you mean?
>>
>>
> 
> 


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] select 5/2???

[Szabo Zoltan]

Which postgres version?
in PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96 :

db=> select -5./2.;
  ?column?
--
  -2.5
(1 row)

CoL

BELLON Michel wrote:

> The good select is
> 
> SELECT 5./2.
> 
> BUT 
> 
> select -5./2.
> +--+
> | ?column? | 
> +--+
> |  2.5 |  not -2.5
> +--+
> 
> 
> 
> Michel BELLON
> LCIE - Informatique appliquée
> 33 (0)1 40 95 60 35
> 
> 
>>-Message d'origine-
>>De:   guard [SMTP:[EMAIL PROTECTED]]
>>Date: mercredi 3 octobre 2001 19:05
>>À:[EMAIL PROTECTED]
>>Objet:select 5/2???
>>
>>dear all
>>
>>I run select 5/2  = 2
>>who to get "2.5"
>>
>>thanks
>>
>>
>>--
>>
>>
>>
>>


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly