Обсуждение: approve VKPts5 unsubscribe pgsql
This is a multi-part message in MIME format. ------=_NextPart_000_0069_01BF9D3C.7C5AFE80 Content-Type: text/plain;charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable ------=_NextPart_000_0069_01BF9D3C.7C5AFE80 Content-Type: text/html;charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content=3D"text/html; charset=3Diso-8859-2" = http-equiv=3DContent-Type> <META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV> </DIV></BODY></HTML> ------=_NextPart_000_0069_01BF9D3C.7C5AFE80--
Hi! 1. I have: vladimir=> explain SELECT acc.ifs_account_id FROM vladimir-> ifs_account acc, vladimir-> ifs_tree_default def, vladimir-> ifs_account_tree_data atd vladimir-> WHERE vladimir-> acc.ifs_status_id = 12 vladimir-> AND atd.ifs_tree_id IN(14,26) vladimir-> AND def.ifs_tree_default_id IN(587,175) vladimir-> AND atd.ifs_account_id = acc.ifs_account_id vladimir-> AND atd.ifs_data_id = def.ifs_data_id; NOTICE: QUERY PLAN: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=757.02rows=1 width=12) (**) -> ??? (cost=757.02 rows=1 width=12) -> Hash Join (cost=757.02 rows=1 width=12) -> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8 ) -> Hash (cost=4.07 rows=3 width=4) -> Index Scan using xpkifs_tree_default,xpkifs_tree_default onifs_tree_default def (cost=4.07 rows=3 width=4) What mean "???" at (**). There is no index? Or unknown method? 2. Seq Scan - Sequential Scan - this mean FULL TABLE SCAN (DATA SCAN)? 3. I try to play with indeces on this tables 3.1. Setting all btree indeces explain SELECT acc.ifs_account_id FROM ifs_account acc, ifs_tree_default def, ifs_account_tree_data atd WHERE acc.ifs_status_id = 12 AND atd.ifs_tree_id IN(14,26) AND def.ifs_tree_default_id IN(587,175) AND atd.ifs_account_id = acc.ifs_account_id AND atd.ifs_data_id = def.ifs_data_id; Nested Loop (cost=0.00 rows=1 width=16) -> Nested Loop (cost=0.00 rows=1 width=12) -> Seq Scan on ifs_tree_defaultdef (cost=0.00 rows=0 width=4) -> Seq Scan on ifs_account_tree_data atd (cost=0.00 rows=0 width=8)-> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) Not optimal way I think 2. I've replace btree indexes on relation AND atd.ifs_data_id = def.ifs_data_id; with: create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); PS: Can't replace on on ifs_account_id because of PRIMARY KEY (may be direct Alternative Entry helps me, but is it right way to optimize?) so: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=10.17rows=1 width=12) -> ??? (cost=10.17 rows=1 width=12) -> Nested Loop (cost=10.17 rows=1 width=12) -> Index Scan using xpkifs_tree_default, xpkifs_tree_default on ifs_tree_default def (cost=4.07 ows=3 width=4) -> Index Scan using xif588ifs_account_tree_data on ifs_account_tree_d ata atd (cost=2.03 rows=3 width=8) 3. I've delete all indexes on AND atd.ifs_data_id = def.ifs_data_id; Then I've got: Nested Loop (cost=0.00 rows=1 width=16) -> Seq Scan on ifs_account acc (cost=0.00 rows=0 width=4) -> Seq Scan (cost=757.02rows=1 width=12) -> ??? (cost=757.02 rows=1 width=12) -> Hash Join (cost=757.02 rows=1width=12) -> Seq Scan on ifs_account_tree_data atd (cost=751.76 rows=3 width=8 ) -> Hash (cost=4.07 rows=3 width=4) -> Index Scan using xpkifs_tree_default,xpkifs_tree_default onifs_tree_default def (cost=4.07 rows=3 width=4) Hash Join appears. But there is no indexes, so "Table Scan" appears (approximately 18000 rows in ifs_account_tree_data)? BAD!?! So, where is the optimal way? To make separate queries and use temporary tables to decrease JOINs? Regards, Alexey V. Meledin InterForge Developers Group, Saint-Petersburg look_to: <www.etcompany.ru><www.businessweb.ru> <www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>> mail_to: <avm@webclub.ru><nick_as: <cureman>>>>
"Alexey V. Meledin" <avm@webclub.ru> writes: > What mean "???" at (**). It's probably a MATERIALIZE node. The 6.5 explain-printer didn't have code to recognize MATERIALIZE, so it prints like that. (Fixed for 7.0.) > Not optimal way I think Possibly not. It's hard to tell when you evidently have never vacuumed your database. Those tiny row counts and near-zero costs are presumably bogus ... but if the planner thinks those tables are small, it's going to generate plans accordingly. You need to run a VACUUM, or preferably VACUUM ANALYZE, to update the statistics the planner needs. > 2. I've replace btree indexes on relation > AND atd.ifs_data_id = def.ifs_data_id; > with: > create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); > create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); Why would you do that? The hash index method doesn't have any advantage over btree that I can see, and it's got a lot of disadvantages. regards, tom lane
Tom Lane wrote: > > 2. I've replace btree indexes on relation > > AND atd.ifs_data_id = def.ifs_data_id; > > with: > > create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); > > create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); > > Why would you do that? The hash index method doesn't have any advantage > over btree that I can see, and it's got a lot of disadvantages. Tom, I have heard this stated several times in this list and yet it contradicts what I was taught in my course on databases. It was explained that using a HASH index could be faster than a BTREE index for direct lookup of an item, however, the tradeoff was that you couldn't do "unequal" comparisons (ie COLUMN < SomeValue). The speed gain was because the HASH index could go directly to the page containing the data while the btree index might need to load several pages to get to the final data, especially for large BTREE indexes. Is this simply not true for PostgreSQL, or do you think it isn't true in general (for most implementations of HASH and BTREE)? Mark -- Mark Dalphin email: mdalphin@amgen.com Mail Stop: 29-2-A phone: +1-805-447-4951 (work) One Amgen Center Drive +1-805-375-0680 (home) Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)
Mark Dalphin <mdalphin@amgen.com> writes: > Tom Lane wrote: >> Why would you do that? The hash index method doesn't have any advantage >> over btree that I can see, and it's got a lot of disadvantages. > Tom, I have heard this stated several times in this list and yet it > contradicts what I was taught in my course on databases. It was > explained that using a HASH index could be faster than a BTREE index > for direct lookup of an item, however, the tradeoff was that you > couldn't do "unequal" comparisons (ie COLUMN < SomeValue). The speed > gain was because the HASH index could go directly to the page > containing the data while the btree index might need to load several > pages to get to the final data, especially for large BTREE indexes. > Is this simply not true for PostgreSQL, or do you think it isn't true > in general (for most implementations of HASH and BTREE)? Hmm. I haven't actually timed hash versus btree lookups in Postgres; it's possible that hash is faster (at least for some tables). I'm not sure I believe that a hash "can go directly to the right page", though. You could go directly to the right hash bucket, but how many index entries will be in the bucket? You might still have to follow a chain of overflow pages. As Professor Knuth remarked about hashing, when you use it you are absolutely depending on the laws of probability: the average case is really good, but the worst case is awful. Btrees have more predictable performance. When I commented that hashes have disadvantages, I was thinking of other issues that are Postgres-specific: PG's hash indexes support fewer data types than our btrees do, and our btrees support concurrent index updates while hashes don't. If you use a hash index you are pretty much back to the bad old pre-MVCC days: you can have N readers *or* one writer at any instant. (I imagine this could be fixed if anyone cared to invest the work.) The btree code is also a lot more heavily used by most people, so I'd expect it to have fewer bugs. And, as you say, btrees support order-related queries while hashes only support equality. IMHO this alone is sufficient to justify choosing btree, unless you are quite certain that you know all the kinds of query that you will be throwing at the table and none of them involve ordering. regards, tom lane
Hi, Tom! Monday, April 03, 2000, 6:32:06 PM, you wrote: >> 2. I've replace btree indexes on relation >> AND atd.ifs_data_id = def.ifs_data_id; >> with: >> create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); >> create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); TL> Why would you do that? The hash index method doesn't have any advantage TL> over btree that I can see, and it's got a lot of disadvantages. But as I understand from documentation, Hash Join is the preferable method of JOIN, because of Seq Scan performed only once (I suppose, that it's not full table scan!?!). Nested Joins performs too many scans for results in JOIN, containing many rows and are so slow on large tables (planner/optimizer problem?). Thereby I've found that it's more efficient to make several queries from hard one (planner/optimizer explains me up to 8 neseted loops on it :(). Additional questions: 1. What is the difference among "Seq Scan" and "Index Scan" in a planer/optimizer query execution plan? Seq Scan - "sequential data scan" or "sequential scan, based on "serial" field"? 2. I have create table aaa (a int4, b int4, c int4, d int4,CONSTRAINT aaa_pk PRIMARY KEY(a,b,c) ); create table bbb ( a int4, b int4, c int4, g serial, f int4 ,CONSTRAINT bbb_pk PRIMARY KEY (a, b, c, g)); create index aaa_index_a on aaa (a); create index aaa_index_b on aaa (b); create index aaa_index_c on aaa (c); create index aaa_index_d on aaa (d); --create index bbb_index_fk on bbb (a,b,c); --FK create index bbb_index_a on bbb (a); create index bbb_index_b on bbb (b); create index bbb_index_c on bbb (c); !PLEASE, verify indexes (I'm not shure at 100%) explain select b.f from bbb b, aaa a where a.b=50001 and a.c=50002 and a.d=50003 -- at this point I have "aaa" rows for join -- performing jon and a.b=b.b and a.c=b.c and a.a=b.a; Nested Loop (cost=4.05 rows=1 width=28) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=12) -> IndexScan using bbb_pk on bbb b (cost=2.00 rows=61984 width=16) explain select b.f from bbb b, aaa a where a.b=50001 and a.c=50002 and a.d=50003 -- at this point I have "aaa" rows for join and b.b=2 andb.c=3 -- at this point I have "bbb" rows for join -- performing jon and a.a=b.a; Nested Loop (cost=4.05 rows=1 width=12) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=4) -> IndexScan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) ================================= Two queries are almost equal, but in the first I perform join by wide PK key. In the second only one key is explicity used in JOIN. So, the difference is in ROWS and WIDTH: PS: Each table has 100000 rows. 1. Nested Loop (cost=4.05 rows=1 width=28) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=12) -> IndexScan using bbb_pk on bbb b (cost=2.00 rows=61984 width=16) 2. Nested Loop (cost=4.05 rows=1 width=12) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=4) -> IndexScan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) So, questions: 1. can I say, that the second query is more optimal then 1-st? 2. Variants I try: 2.1. When I use no indexes, then: 2.1.1. Nested Loop (cost=2442.50 rows=1 width=28) -> Seq Scan on aaa a (cost=2440.50 rows=1 width=12) -> Index Scan usingbbb_pk on bbb b (cost=2.00 rows=61984 width=16) 2.1.2. Nested Loop (cost=2442.50 rows=1 width=12) -> Seq Scan on aaa a (cost=2440.50 rows=1 width=4) -> Index Scan usingbbb_pk on bbb b (cost=2.00 rows=1 width=8) 2.2. I try to set indexes on a.a and b.a, supposing that it helps a bit on relation "a.a=b.a" 2.2.1. Nested Loop (cost=3355.28 rows=1 width=28) -> Seq Scan on aaa a (cost=3353.28 rows=1 width=12) -> Index Scan usingbbb_pk on bbb b (cost=2.00 rows=85159 width=16) 2.2.2. Nested Loop (cost=3355.28 rows=1 width=12) -> Seq Scan on aaa a (cost=3353.28 rows=1 width=4) -> Index Scan usingbbb_pk on bbb b (cost=2.00 rows=1 width=8) Perfomace become lower!! Why? 2.3. I've set only this indexes: create index aaa_index_bcd on aaa (b,c,d); create index bbb_index_bc on bbb (b,c); and take the most better perfomance: 2.3.1. Nested Loop (cost=4.00 rows=1 width=28) -> Index Scan using aaa_index_bcd on aaa a (cost=2.00 rows=1 width=12)-> Index Scan using bbb_pk on bbb b (cost=2.00 rows=85159 width=16) 2.3.2. Nested Loop (cost=4.00 rows=1 width=12) -> Index Scan using aaa_index_bcd on aaa a (cost=2.00 rows=1 width=4) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) 2.4. I add indexes on a.a and a.b 2.4.1. Nested Loop (cost=4.00 rows=1 width=28) -> Index Scan using aaa_index_bcd on aaa a (cost=2.00 rows=1 width=12)-> Index Scan using bbb_pk on bbb b (cost=2.00 rows=85159 width=16) 2.4.2. Nested Loop (cost=4.00 rows=1 width=12) -> Index Scan using aaa_index_bcd on aaa a (cost=2.00 rows=1 width=4) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) Nothing happens! Is it occurs because PostgreSQL makes JOIN on a.a and b.a in memory? Or Use of wide PK is more prefferable than simple index? Can anybody collect results of this small experiment and make right deduction? Regards, Alexey V. Meledin InterForge Developers Group, Saint-Petersburg look_to: <www.etcompany.ru><www.businessweb.ru> <www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>> mail_to: <avm@webclub.ru><nick_as: <cureman>>>>
"Alexey V. Meledin" <avm@webclub.ru> writes: >>> create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); >>> create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); TL> Why would you do that? The hash index method doesn't have any advantage TL> over btree that I can see, and it's got a lot of disadvantages. > But as I understand from documentation, Hash Join is the preferable > method of JOIN, because of Seq Scan performed only once (I suppose, that > it's not full table scan!?!). Where in the documentation did you see that? If it implies that then it's wrong. We could certainly simplify the optimizer a lot if it could just always pick a hash join ;-). But more to the point, whether a hash join is used has nothing to do with what kind of index you have. You do not need a hash index to support a hash join. Hash join means that we build a in-memory hash table containing the relevant rows from the inner relation (discarding any that can be rejected due to single-relation WHERE conditions), and then probe into that table for each row of the outer relation. > Nested Joins performs too many scans for results in JOIN, containing > many rows and are so slow on large tables (planner/optimizer problem?). Could be. The optimizer is a work-in-progress; I wouldn't claim that it always makes the right choices. But without more details it's hard to say if it's doing the wrong thing or not. > 1. What is the difference among "Seq Scan" and "Index Scan" in > a planer/optimizer query execution plan? sequential scan (read whole table sequentially) or index scan (use index to scan just the rows meeting a WHERE condition that matches the index). > So, the difference is in ROWS and WIDTH: > PS: Each table has 100000 rows. > 1. Nested Loop (cost=4.05 rows=1 width=28) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=12) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=61984 width=16) > 2. Nested Loop (cost=4.05 rows=1 width=12) -> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=4) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) > So, questions: > 1. can I say, that the second query is more optimal then 1-st? I'm not sure why 6.5 fails to display a reasonable rows estimate in the first case. Current sources produce a more reasonable result. That rows estimate is clearly wrong, or at least inconsistent with the estimated cost of the indexscan and the estimated number of rows produced by the join. > 2. Variants I try: > 2.1. When I use no indexes, then: > 2.1.1. Nested Loop (cost=2442.50 rows=1 width=28) -> Seq Scan on aaa a (cost=2440.50 rows=1 width=12) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=61984 width=16) > 2.1.2. Nested Loop (cost=2442.50 rows=1 width=12) -> Seq Scan on aaa a (cost=2440.50 rows=1 width=4) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) > 2.2. I try to set indexes on a.a and b.a, supposing that it helps a > bit on relation "a.a=b.a" > 2.2.1. Nested Loop (cost=3355.28 rows=1 width=28) -> Seq Scan on aaa a (cost=3353.28 rows=1 width=12) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=85159 width=16) > 2.2.2. Nested Loop (cost=3355.28 rows=1 width=12) -> Seq Scan on aaa a (cost=3353.28 rows=1 width=4) -> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) > Perfomace become lower!! Why? Did performance *actually* change? I don't see how it could, considering that the plan is the same. I hope you're not confusing the planner's cost estimates with reality ;-). If you're wondering why the estimate changed, it's probably because CREATE INDEX updates the planner's information about number of rows and number of disk pages the relation has. I guess you loaded more data since your last VACUUM. > 2.4. I add indexes on a.a and a.b > Nothing happens! It's already using an index that covers column b, and the index on column a doesn't help because it can only use one index in a scan. regards, tom lane
Hi, Tom! Thanks a lot! The rest small questions are below: Wednesday, April 05, 2000, 8:04:11 PM, you wrote: >> But as I understand from documentation, Hash Join is the preferable >> method of JOIN, because of Seq Scan performed only once (I suppose, that >> it's not full table scan!?!). TL> Where in the documentation did you see that? If it implies that then TL> it's wrong. We could certainly simplify the optimizer a lot if it could TL> just always pick a hash join ;-). nested iteration join: The right relation is scanned once for every tuple found in the left relation. This strategy is easyto implement but can be very time consuming. merge sort join: Each relation is sorted on the join attributes before the join starts. Then the two relations are mergedtogether taking into account that both relations are ordered on the join attributes. This kind of join is more attractivebecause every relation has to be scanned only once. hash join: the right relation is first hashed on its join attributes. Next the left relation is scanned and the appropriatevalues of every tuple found are used as hash keys to locate the tuples in the right relation. There is no direct indication, but there is no words about sequential table scan performed in hash method. So I confused a bit. (possibly because of my poor english :( ). >> Nested Joins performs too many scans for results in JOIN, containing >> many rows and are so slow on large tables (planner/optimizer problem?). TL> Could be. The optimizer is a work-in-progress; I wouldn't claim that it TL> always makes the right choices. But without more details it's hard to TL> say if it's doing the wrong thing or not. for example: planner/optimizer doesn't understand parallel retriving. select a.value, b.value from table a, table b, joint aa, joint bb where a.id=aa.id AND b.id=bb.id AND a.id=aa.id AND bb.value=1 AND aa.value=2; There are two parallel queries. Planner/optimizer can't recognize it well. It explains two many Nested joins, that it's simple to make 3 queries (2 INTO temp tables and 1 performing a join). PS: For PostgreSQL it's (I see) better to rebuild all table if I want to add or delete fields to object of real life (I mean a goods and it's behaviour, for example. This is in general to improve admin interface functionality and possibilities ). >> 1. What is the difference among "Seq Scan" and "Index Scan" in >> a planer/optimizer query execution plan? TL> sequential scan (read whole table sequentially) or index scan (use TL> index to scan just the rows meeting a WHERE condition that matches TL> the index). So if I see that in my query Sequential Scan occur, than that is an alarm in my database model? >> So, the difference is in ROWS and WIDTH: >> PS: Each table has 100000 rows. >> 1. Nested Loop (cost=4.05 rows=1 width=28) ->> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=12) ->> Index Scan using bbb_pk on bbb b (cost=2.00 rows=61984 width=16) >> 2. Nested Loop (cost=4.05 rows=1 width=12) ->> Index Scan using aaa_index_b on aaa a (cost=2.05 rows=1 width=4) ->> Index Scan using bbb_pk on bbb b (cost=2.00 rows=1 width=8) >> So, questions: >> 1. can I say, that the second query is more optimal then 1-st? TL> I'm not sure why 6.5 fails to display a reasonable rows estimate in TL> the first case. Current sources produce a more reasonable result. TL> That rows estimate is clearly wrong, or at least inconsistent with TL> the estimated cost of the indexscan and the estimated number of TL> rows produced by the join. PostgreSQL 6.5.3 on FreeBSD 3.3. build from ports collection. I've verify with vacuum, but nothing happens. :( small bug >> 2. Variants I try: >> 2.1. When I use no indexes, then: >> 2.1.1. Nested Loop (cost=2442.50 rows=1 width=28) >> 2.1.2. Nested Loop (cost=2442.50 rows=1 width=12) >> 2.2. I try to set indexes on a.a and b.a, supposing that it helps a >> bit on relation "a.a=b.a" >> 2.2.1. Nested Loop (cost=3355.28 rows=1 width=28) >> 2.2.2. Nested Loop (cost=3355.28 rows=1 width=12) >> Perfomace become lower!! Why? TL> Did performance *actually* change? I don't see how it could, TL> considering that the plan is the same. I hope you're not confusing TL> the planner's cost estimates with reality ;-). Hmmmm..... BUT HOW CAN I ANALIZE MY QUERY AND INDEXES?????? Confusing .... What to do? I've thought that perfomance changes occur, correlating on index combination optimizer uses. TL> If you're wondering why the estimate changed, it's probably because TL> CREATE INDEX updates the planner's information about number of rows TL> and number of disk pages the relation has. I guess you loaded TL> more data since your last VACUUM. I provide clean experiment. You are right. vacuum rebuilds planner/optimizer statistics. >> 2.4. I add indexes on a.a and a.b >> Nothing happens! TL> It's already using an index that covers column b, and the index on TL> column a doesn't help because it can only use one index in a scan. But if it has the choise, then what rule it implements. Why not to use narrow index in this case? TL> regards, tom lane Regards, Alexey V. Meledin InterForge Developers Group, Saint-Petersburg look_to: <www.etcompany.ru><www.businessweb.ru> <www.inplan.spb.ru><www.pia.ru>>>>>>>>>>>>>>>>> mail_to: <avm@webclub.ru><nick_as: <cureman>>>>