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_.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_, tagimplant3_1_.thr
u_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;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
CrashReporter trace:
Date/Time: 2007-05-31 10:21:39.285 +0200
OS Version: 10.4.9 (Build 8P2137)
Report Version: 4
Command: postmaster
Path: ./bin/postmaster
Parent: postmaster [23091]
Version: ??? (???)
PID: 23096
Thread: 0
Exception: EXC_BAD_ACCESS (0x0001)
Codes: KERN_PROTECTION_FAILURE (0x0002) at 0x00000018
Thread 0 Crashed:
0 postmaster 0x00116ec6 ExecSetSlotDescriptor + 77 (execTuples.c:344)
1 postmaster 0x001182f9 ExecAssignScanTypeFromOuterPlan + 33
(execUtils.c:771)
2 postmaster 0x001240c8 ExecInitSort + 168 (nodeSort.c:211)
3 postmaster 0x0010ee5e ExecInitNode + 678 (execProcnode.c:230)
4 postmaster 0x0012228c ExecInitMergeJoin + 252 (nodeMergejoin.c:1445)
5 postmaster 0x0010eeb8 ExecInitNode + 768 (execProcnode.c:212)
6 postmaster 0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
7 postmaster 0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
8 postmaster 0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
9 postmaster 0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
10 postmaster 0x00122b4e ExecInitNestLoop + 198 (nodeNestloop.c:322)
11 postmaster 0x0010eed6 ExecInitNode + 798 (execProcnode.c:207)
12 postmaster 0x00124d19 ExecInitLimit + 185 (nodeLimit.c:376)
13 postmaster 0x0010edaa ExecInitNode + 498 (execProcnode.c:260)
14 postmaster 0x0010df50 ExecutorStart + 1516 (execMain.c:634)
15 postmaster 0x000cf393 ExplainOnePlan + 146 (explain.c:257)
16 postmaster 0x000cf885 ExplainQuery + 267 (explain.c:170)
17 postmaster 0x001ba9fe PortalRunUtility + 233 (palloc.h:84)
18 postmaster 0x001bafef FillPortalStore + 239 (pquery.c:1050)
19 postmaster 0x001bb672 PortalRun + 994 (pquery.c:792)
20 postmaster 0x001b604d exec_simple_query + 656 (postgres.c:965)
21 postmaster 0x001b805c PostgresMain + 5628 (postgres.c:3507)
22 postmaster 0x00186dfa ServerLoop + 2828 (postmaster.c:2614)
23 postmaster 0x00187f4b PostmasterMain + 2704 (postmaster.c:972)
24 postmaster 0x00133412 main + 1236 (main.c:188)
25 postmaster 0x000024e6 _start + 216
26 postmaster 0x0000240d start + 41
Thread 0 crashed with X86 Thread State (32-bit):
eax: 0x00000000 ebx: 0x0012402e ecx: 0x00000004 edx: 0x00000000
edi: 0x00000000 esi: 0x020775b0 ebp: 0xbfffdca8 esp: 0xbfffdc90
ss: 0x0000001f efl: 0x00010246 eip: 0x00116ec6 cs: 0x00000017
ds: 0x0000001f es: 0x0000001f fs: 0x00000000 gs: 0x00000037
Binary Images Description:
0x1000 - 0x32cfff postmaster
/Users/grant/Development/bin/pgsql/bin/postmaster
0x8fe00000 - 0x8fe4afff dyld 46.12 /usr/lib/dyld
0x90000000 - 0x90170fff libSystem.B.dylib /usr/lib/libSystem.B.dylib
0x95a5a000 - 0x95a5cfff libmathCommon.A.dylib
/usr/lib/system/libmathCommon.A.dylib
Regards,
Grant
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
