[PERFORM] Constraint propagating for equal fields
Hello. I've always thought that PostgreSQL would propagate constraint from field1 to field2 if condition says field1=field2, but this does not seem the case: dict=# explain select * from domain_list,title.domains where processed_at is not null and key=groupid and key < 100 and groupid < 100; QUERY PLAN -- Hash Join (cost=2179918.87..4529994.61 rows=4616 width=318) Hash Cond: (domain_list.key = domains.groupid) -> Bitmap Heap Scan on domain_list (cost=26253.02..2310541.55 rows=870759 width=123) Recheck Cond: (key < 100) -> Bitmap Index Scan on domain_list_new_pkey (cost=0.00..26035.33 rows=870759 width=0) Index Cond: (key < 100) -> Hash (cost=2119232.34..2119232.34 rows=864201 width=195) -> Bitmap Heap Scan on domains (cost=16674.34..2119232.34 rows=864201 width=195) Recheck Cond: (groupid < 100) Filter: (processed_at IS NOT NULL) -> Bitmap Index Scan on dgroup (cost=0.00..16458.29 rows=890154 width=0) Index Cond: (groupid < 100) (12 rows) dict=# explain select * from domain_list,title.domains where processed_at is not null and key=groupid and key < 100 ; QUERY PLAN Hash Join (cost=2337583.04..18222634.81 rows=845372 width=318) Hash Cond: (domains.groupid = domain_list.key) -> Seq Scan on domains (cost=0.00..5423788.20 rows=158280964 width=195) Filter: (processed_at IS NOT NULL) -> Hash (cost=2310541.55..2310541.55 rows=870759 width=123) -> Bitmap Heap Scan on domain_list (cost=26253.02..2310541.55 rows=870759 width=123) Recheck Cond: (key < 100) -> Bitmap Index Scan on domain_list_new_pkey (cost=0.00..26035.33 rows=870759 width=0) Index Cond: (key < 100) (9 rows) dict=# explain select * from domain_list,title.domains where processed_at is not null and key=groupid and groupid < 100; QUERY PLAN Hash Join (cost=2153665.85..16943819.35 rows=862710 width=318) Hash Cond: (domain_list.key = domains.groupid) -> Seq Scan on domain_list (cost=0.00..6887257.54 rows=162753054 width=123) -> Hash (cost=2119232.34..2119232.34 rows=864201 width=195) -> Bitmap Heap Scan on domains (cost=16674.34..2119232.34 rows=864201 width=195) Recheck Cond: (groupid < 100) Filter: (processed_at IS NOT NULL) -> Bitmap Index Scan on dgroup (cost=0.00..16458.29 rows=890154 width=0) Index Cond: (groupid < 100) (9 rows) The first query is the fastest one, but it is equal to both 2 and 3 and I thought PostgreSQL can perform such propagation by itself. Best regards, Vitalii Tymchyshyn.
Re: [PERFORM] Benchmark shows very slow bulk delete
James Mansion wrote: > Ivan Voras wrote: >> I wish that, when people got the idea to run a simplistic benchmark >> like this, they would at least have the common sense to put the >> database on a RAM drive to avoid problems with different cylinder >> speeds of rotational media and fragmentation from multiple runs. > Huh? >> It's tough to benchmark anything involving rotational drives :) > But - how the database organises its IO to maximise the available > bandwidth, limit > avaiodable seeks, and limit avoidable flushes is absolutely key to > realistic performance, > especially on modest everyday hardware. Not everyone has a usage that > justifies > 'enterprise' kit - but plenty of people can benefit from something a > step up from > SQLite. > > If you just want to benchmark query processor efficiency then that's one > scenario > where taking physical IO out of the picture might be justified, but I > don't see a good > reason to suggest that it is 'common sense' to do so for all testing, > and while the > hardware involved is pretty low end, its still a valid data point. > . You are right, of course, for common benchmarking to see what performance can be expected from some setup in some circumstances, but not where the intention is to compare products. You don't have to go the memory drive / SSD route - just make sure the databases always use the same (small) area of the disk drive. signature.asc Description: OpenPGP digital signature
