Postgres Pro
Downloads
Home   >   mailing lists

Thread: help need it

help need it

From
rute solipa
Date:
i've an oracle query:

Select tb_users.ds_description, tb_users.cd_role, tb_users.cd_user, 
tb_users.ds_login, tb_monitores.cod_equipa from tb_users, tb_monitores 
where ds_login like 'varLogin' and ds_password like 'varPassword' and 
tb_users.cd_user = tb_monitores.cd_user(+)

how can i transform it to an postgresql query?


best regards

etur

Re: help need it

From
Stephan Szabo
Date:
On Tue, 8 Apr 2003, rute solipa wrote:

> i've an oracle query:
>
> Select tb_users.ds_description, tb_users.cd_role, tb_users.cd_user,
> tb_users.ds_login, tb_monitores.cod_equipa from tb_users, tb_monitores
> where ds_login like 'varLogin' and ds_password like 'varPassword' and
> tb_users.cd_user = tb_monitores.cd_user(+)
>
> how can i transform it to an postgresql query?

Should be something like:

Select tb_users.ds_description, tb_users.cd_role, tb_users.cd_user,
tb_users.ds_login, tb_monitores.cod_equipa from tb_users left
outer join tb_monitores using (cd_user)
where ds_login like 'varLogin' and ds_password like 'varPassword';

Re: help need it

From
Achilleus Mantzios
Date:
On Tue, 8 Apr 2003, rute solipa wrote:

> i've an oracle query:
> 
> Select tb_users.ds_description, tb_users.cd_role, tb_users.cd_user, 
> tb_users.ds_login, tb_monitores.cod_equipa from tb_users, tb_monitores 
> where ds_login like 'varLogin' and ds_password like 'varPassword' and 
> tb_users.cd_user = tb_monitores.cd_user(+)
> 
> how can i transform it to an postgresql query?

Can you check the postgresql manual if the (+) operator
means something related to OUTER joins?
If yes then use [LEFT|RIGHT] OUTER JOIN of postgresql.

> 
> 
> best regards
> 
> etur
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

7.3 analyze & vacuum analyze problem

From
Achilleus Mantzios
Date:
Hi,
i think i have an issue regarding the statistics that 
a) (plain) ANALYZE status and 
b) VACUUM ANALYZE status
produce.

I have a table status:
dynacom=# \d status                                  Table "public.status"  Column    |           Type           |
              Modifiers
 
-------------+--------------------------+---------------------------------------------------id          | integer
          | not null default 
 
nextval('"status_id_seq"'::text)checkdate   | timestamp with time zone |assettable  | character varying(50)
|assetidval | integer                  |appname     | character varying(100)   |apptblname  | character varying(50)
|apptblidval| integer                  |colname     | character varying(50)    |colval      | double precision
|status     | character varying(5)     |isvalid     | boolean                  |username    | character varying(50)
|
Indexes: status_id_key unique btree (id),        status_all btree (assettable, assetidval, appname, apptblname, 
status, isvalid),        status_all_wo_astidval btree (assettable, appname, apptblname, 
status, isvalid),        status_appname btree (appname),        status_apptblidval btree (apptblidval),
status_apptblnamebtree (apptblname),        status_assetidval btree (assetidval),        status_assettable btree
(assettable),       status_checkdate btree (checkdate),        status_colname btree (colname),        status_isvalid
btree(isvalid),        status_status btree (status)
 
dynacom=#
dynacom=# SELECT count(*) from status ;count
-------33565
(1 row)
dynacom=#

I very often perform queries of the form:
select count(*) from status where assettable='vessels' and 
appname='ISM PMS' and apptblname='items' and status='warn' 
and isvalid and assetidval=<SOME ID>;

Altho i dont understand exactly why the stats created by
VACUUM ANALYZE are more accurate (meaning producing faster plans)
than the ones created by
plain ANALYZE, (altho for some attributes they are false for sure)
the performance is much much better when
VACUUM ANALYZE is run than plain ANALYZE.

In the former case, some times the status_all index is used,
and sometimes (when the selectivity is small)
a sequential scan is performed.

In the latter case, no index is ever used even  
for crazy statements (assetidval is always >0) like:

select count(*) from status where assettable='vessels' and
appname='ISM PMS' and apptblname='items' and status='warn'
and isvalid and assetidval=-10000000;

I attach the statistics of either case.

My app just performs the above query for most of the assetidval values
(And for all most popular assetidval values)
So the elapsed time of the app i think is a good
measure of the overall performance of these queries.

In the "VACUUM ANALYZE" case it takes 1.2 - 1.5 secs, while
in the "ANALYZE" case it takes >=3+

-- 
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

Re: 7.3 analyze & vacuum analyze problem

From
Josh Berkus
Date:
Achilleus,

> i think i have an issue regarding the statistics that
> a) (plain) ANALYZE status and
> b) VACUUM ANALYZE status
> produce.

It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
after an ANALYZE ... after all, you just vacuumed it, didn't you?

If you're demonstrating some other kind of behavioural difference, then please
post the results of EXPLAIN ANALYZE for the two examples.

Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
list.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: 7.3 analyze & vacuum analyze problem

From
Achilleus Mantzios
Date:
On Wed, 30 Apr 2003, Josh Berkus wrote:

> Achilleus,
>
> > i think i have an issue regarding the statistics that
> > a) (plain) ANALYZE status and
> > b) VACUUM ANALYZE status
> > produce.
>
> It's perfectly normal for a query to run faster after a VACUUM ANALYZE than
> after an ANALYZE ... after all, you just vacuumed it, didn't you?

I am afraid it is not so simple.
What i (unsuccessfully) implied is that
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# ANALYZE status ;
ANALYZE
dynacom=#

is enuf to damage the performance.

>
> If you're demonstrating some other kind of behavioural difference, then please
> post the results of EXPLAIN ANALYZE for the two examples.
>
dynacom=# ANALYZE status ;
ANALYZE
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;

                   QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4309.53..4309.53 rows=1 width=0) (actual
time=242.60..242.60 rows=1 loops=1)
   ->  Seq Scan on status  (cost=0.00..4306.08 rows=1378 width=0) (actual
time=15.75..242.51 rows=50 loops=1)
         Filter: ((assettable = 'vessels'::character varying) AND (appname
= 'ISM PMS'::character varying) AND (apptblname = 'items'::character
varying) AND (status = 'warn'::character varying) AND isvalid AND
(assetidval = 49))
 Total runtime: 242.74 msec
(4 rows)

dynacom=#
dynacom=# VACUUM ANALYZE status ;
VACUUM
dynacom=# EXPLAIN ANALYZE select count(*) from status where
assettable='vessels' and appname='ISM PMS' and apptblname='items' and
status='warn' and isvalid and assetidval=49;

               QUERY PLAN


-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2274.90..2274.90 rows=1 width=0) (actual time=8.89..8.89
rows=1 loops=1)   ->  Index Scan using status_all on status
(cost=0.00..2274.34 rows=223 width=0) (actual time=8.31..8.83 rows=50
loops=1)
         Index Cond: ((assettable = 'vessels'::character varying) AND
(assetidval = 49) AND (appname = 'ISM PMS'::character varying) AND
(apptblname = 'items'::character varying) AND (status = 'warn'::character
varying))
         Filter: isvalid
 Total runtime: 8.98 msec
(5 rows)

dynacom=#

> Oh, and we should probably shift this discussion to the PGSQL-PERFORMANCE
> list.
>

OK.

>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:    +30-210-8981112
fax:    +30-210-8981877
email:  achill@matrix.gatewaynet.com
        mantzios@softlab.ece.ntua.gr