Hi,
This is on Intel OSX, anon CVS download today.
Build process:-
1. make distclean
2. ./configure --enable-debug --enable-cassert --enable-integer-datetimes
--prefix=/Users/grant/Development/bin/pgsql --enable-depend
3. make all install
The query with no EXPLAIN (ANALYSE) completes fine.
The query with EXPLAIN ANALYSE completes fine.
foo=# explain analyse select this_.id as id6_2_, this_1_.created_at as
created2_6_2_, this_1_.created_by as created3_6_2_, this_1_.updated_at as
updated4_6_2_, this_1_.updated_by as updated5_6_2_, this_1_.from_date as
from6_6_2_, this_1_.party_id as party8_6_2_, this_1_.thru_date as thru7_6_2_,
this_1_.type_id as type9_6_2_, this_.tag as tag14_2_, this_.taggedBy_id as
taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as
id3_0_, partyrolet2_.created_at as created2_3_0_, partyrolet2_.created_by as
created3_3_0_, partyrolet2_.updated_at as updated4_3_0_,
partyrolet2_.updated_by as updated5_3_0_, partyrolet2_.description as
descript6_3_0_, partyrolet2_.name as name3_0_, tagimplant3_.id as id6_1_,
tagimplant3_1_.created_at as created2_6_1_, tagimplant3_1_.created_by as
created3_6_1_, tagimplant3_1_.updated_at as updated4_6_1_,
tagimplant3_1_.updated_by as updated5_6_1_, tagimplant3_1_.from_date as
from6_6_1_, tagimplant3_1_.party_id as party8_6_1_, tagimplant
3_1_.thru_date as thru7_6_1_, tagimplant3_1_.type_id as type9_6_1_ from
tagged_asset this_ inner join party_role this_1_ on this_.id=this_1_.id inner
join party_role_type partyrolet2_ on this_1_.type_id=partyrolet2_.id left outer
join tag_implanter tagimplant3_ on this_.taggedBy_id=tagimplant3_.id left outer
join party_role tagimplant3_1_ on tagimplant3_.id=tagimplant3_1_.id where
(lower(this_.tag) like '1f76%') limit 100;
QUERY PLAN
--
Limit (cost=8.31..24.50 rows=1 width=3748) (actual time=23.057..209.191
rows=77 loops=1)
-> Nested Loop (cost=8.31..24.50 rows=1 width=3748) (actual
time=23.055..209.142 rows=77 loops=1)
-> Nested Loop Left Join (cost=8.31..24.22 rows=1 width=2170)
(actual time=23.036..208.326 rows=77 loops=1)
-> Nested Loop Left Join (cost=8.31..18.62 rows=1 width=1098)
(actual time=23.033..208.204 rows=77 loops=1)
-> Merge Join (cost=8.31..10.34 rows=1 width=1094)
(actual time=23.024..208.015 rows=77 loops=1)
Merge Cond: (this_1_.id = this_.id)
-> Index Scan Backward using party_role_pkey on
party_role this_1_ (cost=0.00..18672.18 rows=581325 width=1076) (actual
time=0.102..142.963 rows=240384 loops=1)
-> Sort (cost=8.31..8.32 rows=1 width=22) (actual
time=0.856..0.902 rows=77 loops=1)
Sort Key: this_.id
Sort Method: quicksort Memory: 20kB
-> Index Scan using tagged_asset_tag_key on
tagged_asset this_ (cost=0.01..8.30 rows=1 width=22) (actual time=0.109..0.739
rows=77 loops=1)
Index Cond: ((lower((tag)::text) >=
'1f76'::text) AND (lower((tag)::text) < '1f77'::text))
Filter: (lower((tag)::text) ~~
'1f76%'::text)
-> Index Scan using tag_implanter_pkey on tag_implanter
tagimplant3_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.001..0.001 rows=0
loops=77)
Index Cond: (this_.taggedby_id = tagimplant3_.id)
-> Index Scan using party_role_pkey on party_role
tagimplant3_1_ (cost=0.00..5.59 rows=1 width=1076) (actual time=0.000..0.000
rows=0 loops=77)
Index Cond: (tagimplant3_.id = tagimplant3_1_.id)
-> Index Scan using party_role_type_pkey on party_role_type
partyrolet2_ (cost=0.00..0.27 rows=1 width=1578) (actual time=0.008..0.009 rows=1
loops=77)
Index Cond: (partyrolet2_.id = this_1_.type_id)
Total runtime: 209.699 ms
(20 rows)
However, with just EXPLAIN (no ANALYSE)
foo=# explain select this_.id as id6_2_, this_1_.created_at as created2_6_2_,
this_1_.created_by as created3_6_2_, this_1_.updated_at as updated4_6_2_,
this_1_.updated_by as updated5_6_2_, this_1_.from_date as from6_6_2_,
this_1_.party_id as party8_6_2_, this_1_.thru_date as thru7_6_2_,
this_1_.type_id as type9_6_2_, this_.tag as tag14_2_, this_.taggedBy_id as
taggedBy4_14_2_, this_.taggedDate as taggedDate14_2_, partyrolet2_.id as
id3_0_, partyrolet2_.created_at as created2_3_0_, partyrolet2_.created_by as
created3_3_0_, partyrolet2_.updated_at as updated4_3_0_,
partyrolet2_.u
