Обсуждение: help need it
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
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';
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
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
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
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