[ADMIN] Query optimization path
Hi all, I have the following query: SELECT count(1) FROM providers p JOIN class_default cdUSING (id_provider) JOIN user_data udUSING (id_class) JOIN v_user_traffic ut USING (id_user) WHERE id_user_status in (4,5) AND p.company = 'X'; is not slow but I notice that if I do explain analyze with the table reordered inside the select in another way the cost change. --- FIRST CASE -- explain analyze SELECT count(1) FROM providers p JOIN class_default cdUSING (id_provider) JOIN user_data udUSING (id_class) JOIN v_user_traffic ut USING (id_user) WHERE id_user_status in (4,5) AND p.company = 'SOL'; NOTICE: QUERY PLAN: Aggregate (cost=9482.53..9482.53 rows=1 width=32) (actual time=164.82..164.82 rows=1 loops=1) -> Hash Join (cost=145.89..9480.58 rows=782 width=32) (actual time=77.29..164.16 rows=396 loops=1) -> Hash Join (cost=7.15..9232.71 rows=19870 width=12) (actual time=1.67..152.21 rows=1170 loops=1) -> Seq Scan on user_traffic u (cost=0.00..8877.83 rows=19870 width=8) (actual time=0.23..145.39 rows=1170 loops=1) -> Hash (cost=6.52..6.52 rows=252 width=4) (actual time=0.85..0.85 rows=0 loops=1) -> Seq Scan on contracts c (cost=0.00..6.52 rows=252 width=4) (actual time=0.04..0.52 rows=181 loops=1) -> Hash (cost=138.05..138.05 rows=276 width=20) (actual time=8.88..8.88 rows=0 loops=1) -> Nested Loop (cost=4.02..138.05 rows=276 width=20) (actual time=1.53..7.87 rows=520 loops=1) -> Hash Join (cost=4.02..5.29 rows=1 width=12) (actual time=0.98..1.14 rows=1 loops=1) -> Seq Scan on class_default cd (cost=0.00..1.18 rows=18 width=8) (actual time=0.02..0.09 rows=18 loops=1) -> Hash (cost=4.01..4.01 rows=1 width=4) (actual time=0.21..0.21 rows=0 loops=1) -> Seq Scan on providers p (cost=0.00..4.01 rows=1 width=4) (actual time=0.19..0.19 rows=1 loops=1) -> Index Scan using idx_user_data_class on user_data ud (cost=0.00..127.99 rows=382 width=8) (actual time=0.52..5.32 rows=520 loops=1) Total runtime: 165.23 msec --- SECOND CASE -- explain analyze SELECT count(1) FROM user_data ud JOIN v_user_traffic ut USING (id_user) JOIN class_default cd USING (id_class) JOIN providers p USING (id_provider) WHERE id_user_status in (4,5) and p.company = 'SOL'; NOTICE: QUERY PLAN: Aggregate (cost=10194.82..10194.82 rows=1 width=32) (actual time=210.09..210.09 rows=1 loops=1) -> Hash Join (cost=324.95..10194.38 rows=174 width=32) (actual time=123.18..209.47 rows=396 loops=1) -> Hash Join (cost=320.94..10117.81 rows=14076 width=28) (actual time=54.17..206.00 rows=1167 loops=1) -> Hash Join (cost=319.71..9870.25 rows=14076 width=20) (actual time=53.10..199.45 rows=1167 loops=1) -> Hash Join (cost=7.15..9232.71 rows=19870 width=12) (actual time=1.61..142.42 rows=1170 loops=1) -> Seq Scan on user_traffic u (cost=0.00..8877.83 rows=19870 width=8) (actual time=0.23..135.88 rows=1170 loops=1) -> Hash (cost=6.52..6.52 rows=252 width=4) (actual time=0.81..0.81 rows=0 loops=1) -> Seq Scan on contracts c (cost=0.00..6.52 rows=252 width=4) (actual time=0.05..0.51 rows=181 loops=1) -> Hash (cost=300.15..300.15 rows=4966 width=8) (actual time=50.89..50.89 rows=0 loops=1) -> Seq Scan on user_data ud (cost=0.00..300.15 rows=4966 width=8) (actual time=0.27..42.02 rows=4978 loops=1) -> Hash (cost=1.18..1.18 rows=18 width=8) (actual time=0.33..0.33 rows=0 loops=1) -> Seq Scan on class_default cd (cost=0.00..1.18 rows=18 width=8) (actual time=0.25..0.30 rows=18 loops=1) -> Hash (cost=4.01..4.01 rows=1 width=4) (actual time=0.58..0.58 rows=0 loops=1) -> Seq Scan on providers p (cost=0.00..4.01 rows=1 width=4) (actual time=0.56..0.56 rows=1 loops=1) Total runtime: 210.41 msec I was believing that postgres before to do the query choose the combination that cost less, and in this case ( less then 11 table, I have geqo_threshold = 11 ) in an exaustive way. Why I obtain two different cost ? Note also that in the first case postgres use and index and not in the second. Ciao Gaetano. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] crypto?
Laurette Cisneros writes: > On Suse 8.0, I'm trying to compile postgresql 7.3.1. > > configure gives the following error: > checking for inflate in -lz... yes > checking for CRYPTO_new_ex_data in -lcrypto... no > configure: error: library 'crypto' is required for OpenSSL > > Yast2 doesn't show me any packages for crypto that can be installed. openssl-devel -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Query optimization path
On Sun, Jan 12, 2003 at 17:31:34 +0100, Gaetano Mendola <[EMAIL PROTECTED]> wrote: > Hi all, > > I have the following query: > > SELECT count(1) > FROM providers p JOIN class_default cdUSING (id_provider) > JOIN user_data udUSING (id_class) > JOIN v_user_traffic ut USING (id_user) > WHERE id_user_status in (4,5) AND > p.company = 'X'; > > is not slow but I notice that if I do explain analyze with the table > reordered > inside the select in another way the cost change. Using "JOIN" fixes the order that tables are joined in. If you are just doing inner joins, then you probably don't want to use the "JOIN" keyword. The exception being when there are so many tables being joined that you want to manually specify at least some of the join ordering in order to help out the planner. Outer (and left and right) joins are generally not associative, so the "JOIN" keyword being required for those cases isn't generally going to be a problem. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Query optimization path
"Bruno Wolff III" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Gaetano Mendola <[EMAIL PROTECTED]> wrote: > > Hi all, > > > > I have the following query: > > > > SELECT count(1) > > FROM providers p JOIN class_default cdUSING (id_provider) > > JOIN user_data udUSING (id_class) > > JOIN v_user_traffic ut USING (id_user) > > WHERE id_user_status in (4,5) AND > > p.company = 'X'; > > > > is not slow but I notice that if I do explain analyze with the table > > reordered > > inside the select in another way the cost change. > Using "JOIN" fixes the order that tables are joined in. If you are > just doing inner joins, then you probably don't want to use the "JOIN" > keyword. The exception being when there are so many tables being joined > that you want to manually specify at least some of the join ordering in > order to help out the planner. And where is written this behavior ? Is it SQL standard ? Ciao Gaetano. ---(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
Re: [ADMIN] Query optimization path
On Sun, 12 Jan 2003, Gaetano Mendola wrote: > "Bruno Wolff III" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Gaetano Mendola <[EMAIL PROTECTED]> wrote: > > > Hi all, > > > > > > I have the following query: > > > > > > SELECT count(1) > > > FROM providers p JOIN class_default cdUSING (id_provider) > > > JOIN user_data udUSING (id_class) > > > JOIN v_user_traffic ut USING (id_user) > > > WHERE id_user_status in (4,5) AND > > > p.company = 'X'; > > > > > > is not slow but I notice that if I do explain analyze with the table > > > reordered > > > inside the select in another way the cost change. > > > Using "JOIN" fixes the order that tables are joined in. If you are > > just doing inner joins, then you probably don't want to use the "JOIN" > > keyword. The exception being when there are so many tables being joined > > that you want to manually specify at least some of the join ordering in > > order to help out the planner. > > And where is written this behavior ? Is it SQL standard ? http://www.postgresql.org/idocs/index.php?explicit-joins.html The standard generally has nothing to say about optimization. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Query optimization path
On Sun, Jan 12, 2003 at 18:07:14 +0100, Gaetano Mendola <[EMAIL PROTECTED]> wrote: > "Bruno Wolff III" <[EMAIL PROTECTED]> wrote in message > > > Using "JOIN" fixes the order that tables are joined in. If you are > > just doing inner joins, then you probably don't want to use the "JOIN" > > keyword. The exception being when there are so many tables being joined > > that you want to manually specify at least some of the join ordering in > > order to help out the planner. > > And where is written this behavior ? Is it SQL standard ? The SQL standard isn't going to address performance, just results. Look in section 10.3 of the User's Guide. This is under Performance Tips and Controlling the Planner with Explicit JOIN Clauses. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Multibyte characters in object names
Hi, Does Postgres allow multibyte characters (e.g. japanese kanji) in table names, column names etc ? If yes, do I have to do any special setting? Japanese characters as data has been no problem at all. I have setup both the server (database) and client (psql) as EUC_JP encoding. I am using Postgres 7.2.3 on Red hat 7.3. regards Siva ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] How can I replace 'Enter Character' to 'balnk space character'
Hi All, May I know how can I replace 'Enter Character' to 'balnk space' character, while retreiving data from my database. Sreedhar "Faith, faith, faith in ourselves, faith, faith in God, this is the secret of greatness. If you have faith in all the three hundred and thirty millions of your mythological gods, and in all the gods which foreigners have now and again introduced into your midst, and still have no faith in yourselves, there is no salvation for you. " (III. 190) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
