> > 6.5.3 : [ nested loop ]
> > 7.0RC3 (with the exact same data) : [ merge join ]
>
> So, may I have the envelope please? What's the timings?
Eh'? I cut and pasted everything that was printed...
Here it is again, with both the beginning and ending prompts :-)
7.0 :
ipa=# explain select * from applicants as a,applicants_states as s where
a.firstname ~* '^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10
offset 0;
NOTICE: QUERY PLAN:
Hash Join (cost=1355.82..5943.73 rows=17 width=615) -> Seq Scan on applicants_states s (cost=0.00..4492.54
rows=2350
width=16) -> Hash (cost=1355.54..1355.54 rows=112 width=599) -> Seq Scan on applicants a (cost=0.00..1355.54
rows=112
width=599)
EXPLAIN
ipa=#
6.5.3 :
ipa=> explain select * from applicants as a,applicants_states as s where
a.firstname ~* '^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10
offset 0;
NOTICE: QUERY PLAN:
Nested Loop (cost=1693.76 rows=6 width=615) -> Seq Scan on applicants a (cost=1554.71 rows=50 width=599) -> Index
Scanusing applicants_states_app_id on applicants_states s
(cost=2.78 rows=1023 width=16)
EXPLAIN
ipa=>
> Oh, that's interesting. I doubt that date_ops will work very well on
> timestamp data (or on its predecessor datetime). But 7.0 is the first
> version that actually checks whether your requested index operators are
> compatible with the column datatype --- previous versions would blindly
> do what you told them to, and very possibly coredump depending on what
> the datatypes in question where. I wonder if that mistake was causing
> some of the instability you had with 6.5?
It's very likely that had to do with the stability problems, I indexed all
the datetime fields like that..
I index the datetime fields as you mentioned previously in your email and
WOW, the speed improvement was crazy. It's damn near instant if you're
searching just by date created (as many of my lovely users do!)..
-Mitch