[SQL] Different Query plans filtering between dates

2007-12-24 Thread Dani Castaños 

Hi all!

I'm trying to find out a solution for this.
I paste you two queries and their query plans:

SELECT se.enduser_id
   , se.enduser_number
   , se.points
   , se.total_messages
   , sm.message_order
   , to_char( se.creation_time, 'DD/MM/ HH24:MI:SS' ) as first
   , MAX( to_char( s.timestamp_in, 'DD/MM/ HH24:MI:SS' ) ) as last
   , s.telecom_operator_id
FROM sequence_enduser se
 , sequence_messages sm
 , statistics s
 , statistics_sequence ss
WHERE se.customer_app_config_id = 36052
AND se.current_message_id = sm.sequence_message_id
AND se.enduser_id = ss.enduser_id
AND ss.statistic_id = s.statistic_id
AND s.telecom_operator_id <> 0
AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071217 00', 'MMDD HH24' 
) AND TO_TIMESTAMP( '20071224 13', 'MMDD HH24' )
GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages, 
sm.message_order, se.creation_time, s.telecom_operator_id

ORDER BY enduser_number ASC

   
QUERY PLAN 
- 

Sort  (cost=31324.84..31513.34 rows=75402 width=56) (actual 
time=3274.487..3282.754 rows=20275 loops=1)

 Sort Key: se.enduser_number
 ->  GroupAggregate  (cost=22200.32..25216.40 rows=75402 width=56) 
(actual time=2487.328..3028.551 rows=20275 loops=1)
   ->  Sort  (cost=22200.32..22388.83 rows=75402 width=56) (actual 
time=2487.218..2519.218 rows=75367 loops=1)
 Sort Key: se.enduser_id, se.enduser_number, se.points, 
se.total_messages, sm.message_order, se.creation_time, 
s.telecom_operator_id
 ->  Hash Join  (cost=8697.77..16091.89 rows=75402 
width=56) (actual time=538.019..1812.511 rows=75367 loops=1)

   Hash Cond: ("outer".enduser_id = "inner".enduser_id)
   ->  Merge Join  (cost=7557.54..13066.61 rows=75402 
width=25) (actual time=461.141..1599.325 rows=75367 loops=1)
 Merge Cond: ("outer".statistic_id = 
"inner"."?column3?")
 ->  Index Scan using pk_st_statistic_id on 
"statistics" s  (cost=0.00..3800.23 rows=75373 width=28) (actual 
time=0.133..836.972 rows=75367 loops=1)
   Filter: ((telecom_operator_id <> 
0::numeric) AND (timestamp_in >= to_timestamp('20071217 00'::text, 
'MMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224 
13'::text, 'MMDD HH24'::text)))
 ->  Sort  (cost=7557.54..7750.19 rows=77061 
width=16) (actual time=460.925..495.810 rows=76940 loops=1)

   Sort Key: (ss.statistic_id)::numeric
   ->  Seq Scan on statistics_sequence ss  
(cost=0.00..1302.61 rows=77061 width=16) (actual time=0.014..106.970 
rows=77046 loops=1)
   ->  Hash  (cost=1088.38..1088.38 rows=20740 
width=39) (actual time=76.854..76.854 rows=20285 loops=1)
 ->  Hash Join  (cost=20.02..1088.38 rows=20740 
width=39) (actual time=0.321..55.377 rows=20285 loops=1)
   Hash Cond: ("outer".current_message_id = 
"inner".sequence_message_id)
   ->  Seq Scan on sequence_enduser se  
(cost=0.00..757.25 rows=20740 width=41) (actual time=0.010..27.174 
rows=20285 loops=1)
 Filter: (customer_app_config_id = 
36052)
   ->  Hash  (cost=19.82..19.82 rows=82 
width=6) (actual time=0.303..0.303 rows=101 loops=1)
 ->  Seq Scan on sequence_messages 
sm  (cost=0.00..19.82 rows=82 width=6) (actual time=0.102..0.215 
rows=101 loops=1)

Total runtime: 3321.379 ms



SELECT se.enduser_id
   , se.enduser_number
   , se.points
   , se.total_messages
   , sm.message_order
   , to_char( se.creation_time, 'DD/MM/ HH24:MI:SS' ) as first
   , MAX( to_char( s.timestamp_in, 'DD/MM/ HH24:MI:SS' ) ) as last
   , s.telecom_operator_id
FROM sequence_enduser se
 , sequence_messages sm
 , statistics s
 , statistics_sequence ss
WHERE se.customer_app_config_id = 36052
AND se.current_message_id = sm.sequence_message_id
AND se.enduser_id = ss.enduser_id
AND ss.statistic_id = s.statistic_id
AND s.telecom_operator_id <> 0
AND s.timestamp_in BE

Re: [SQL] Different Query plans filtering between dates

2007-12-24 Thread Pavel Stehule 
Hello,

it's known problem of some BETWEEN a AMD a. You can find some other
info in archive I thing.

Please check if low is equal high and then don't use BETWEEN.

if a  = b then
   select ... where some = a
else
  select ... where some between a and b

Regards
Pavel Stehule

On 24/12/2007, Dani Castaños <[EMAIL PROTECTED]> wrote:
> Hi all!
>
> I'm trying to find out a solution for this.
> I paste you two queries and their query plans:
>
> SELECT se.enduser_id
> , se.enduser_number
> , se.points
> , se.total_messages
> , sm.message_order
> , to_char( se.creation_time, 'DD/MM/ HH24:MI:SS' ) as first
> , MAX( to_char( s.timestamp_in, 'DD/MM/ HH24:MI:SS' ) ) as last
> , s.telecom_operator_id
> FROM sequence_enduser se
>   , sequence_messages sm
>   , statistics s
>   , statistics_sequence ss
> WHERE se.customer_app_config_id = 36052
>  AND se.current_message_id = sm.sequence_message_id
>  AND se.enduser_id = ss.enduser_id
>  AND ss.statistic_id = s.statistic_id
>  AND s.telecom_operator_id <> 0
>  AND s.timestamp_in BETWEEN TO_TIMESTAMP( '20071217 00', 'MMDD HH24'
> ) AND TO_TIMESTAMP( '20071224 13', 'MMDD HH24' )
> GROUP BY se.enduser_id, se.enduser_number, se.points, se.total_messages,
> sm.message_order, se.creation_time, s.telecom_operator_id
> ORDER BY enduser_number ASC
>
>
> QUERY PLAN
> -
>
> Sort  (cost=31324.84..31513.34 rows=75402 width=56) (actual
> time=3274.487..3282.754 rows=20275 loops=1)
>   Sort Key: se.enduser_number
>   ->  GroupAggregate  (cost=22200.32..25216.40 rows=75402 width=56)
> (actual time=2487.328..3028.551 rows=20275 loops=1)
> ->  Sort  (cost=22200.32..22388.83 rows=75402 width=56) (actual
> time=2487.218..2519.218 rows=75367 loops=1)
>   Sort Key: se.enduser_id, se.enduser_number, se.points,
> se.total_messages, sm.message_order, se.creation_time,
> s.telecom_operator_id
>   ->  Hash Join  (cost=8697.77..16091.89 rows=75402
> width=56) (actual time=538.019..1812.511 rows=75367 loops=1)
> Hash Cond: ("outer".enduser_id = "inner".enduser_id)
> ->  Merge Join  (cost=7557.54..13066.61 rows=75402
> width=25) (actual time=461.141..1599.325 rows=75367 loops=1)
>   Merge Cond: ("outer".statistic_id =
> "inner"."?column3?")
>   ->  Index Scan using pk_st_statistic_id on
> "statistics" s  (cost=0.00..3800.23 rows=75373 width=28) (actual
> time=0.133..836.972 rows=75367 loops=1)
> Filter: ((telecom_operator_id <>
> 0::numeric) AND (timestamp_in >= to_timestamp('20071217 00'::text,
> 'MMDD HH24'::text)) AND (timestamp_in <= to_timestamp('20071224
> 13'::text, 'MMDD HH24'::text)))
>   ->  Sort  (cost=7557.54..7750.19 rows=77061
> width=16) (actual time=460.925..495.810 rows=76940 loops=1)
> Sort Key: (ss.statistic_id)::numeric
> ->  Seq Scan on statistics_sequence ss
> (cost=0.00..1302.61 rows=77061 width=16) (actual time=0.014..106.970
> rows=77046 loops=1)
> ->  Hash  (cost=1088.38..1088.38 rows=20740
> width=39) (actual time=76.854..76.854 rows=20285 loops=1)
>   ->  Hash Join  (cost=20.02..1088.38 rows=20740
> width=39) (actual time=0.321..55.377 rows=20285 loops=1)
> Hash Cond: ("outer".current_message_id =
> "inner".sequence_message_id)
> ->  Seq Scan on sequence_enduser se
> (cost=0.00..757.25 rows=20740 width=41) (actual time=0.010..27.174
> rows=20285 loops=1)
>   Filter: (customer_app_config_id =
> 36052)
> ->  Hash  (cost=19.82..19.82 rows=82
> width=6) (actual time=0.303..0.303 rows=101 loops=1)
>   ->  Seq Scan on sequence_messages
> sm  (cost=0.00..19.82 rows=82 width=6) (actual time=0.102..0.215
> rows=101 loops=1)
> Total runtime: 3321.379 ms
>
>
>
> SELECT se.enduser_id
> , se.enduser_number
> , se.points
> , se.total_messages
> , sm.message_order
> , to_char( se.creation_time, 'DD/MM/ HH24:MI:SS' ) as

[SQL] slow query - only uses indices

2007-12-24 Thread Marc 
Hey Folks,

This query is running really slowly.  Sometimes much slower then others.  I
have a feeling that there may be contention on one of the indices it is
using.  In the explain plan, it looks like it estimates 2 rows but actually
finds 228 rows?  Is that really bad?


Query and explain plan are below.  Seems like it spend the most time doing
Index Scan using i_tablea_atextfield on tablea ru
(cost=0.00..2265.28rows=2 width=12) (actual time=
0.624..881.313 rows=228 loops=1)

Any suggestions?

SELECT z.atextfield,
   z.btextfield,
   z.abigintfield,
   p.achar255field, p.ptextfield,
   z.achar1field, u.aboolfield,
   z.textfield1,
   z.achar8field,
   z.achar16field
FROM tablea ru
INNER JOIN tableb u ON ( u.atextfield = ru.anothertextfield )
INNER JOIN tablec z ON u.atextfield = z.atextfield
INNER JOIN tabled p ON p.id = z.pid
LEFT JOIN tablee m ON u.atextfield = m.atextfield  AND m.boolcol5
WHERE ru.atextfield = 'thelookupval'
  AND u.boolcol1 IS TRUE
  AND u.boolcol2 IS FALSE
  AND ru.achar1field <> 'N'
  AND ru.boolcol3 IS FALSE
  AND NOT EXISTS ( SELECT 'x' FROM tablea fru WHERE fru.atextfield =
ru.anothertextfield AND fru.boolcol3 IS TRUE )
  AND EXISTS ( SELECT 'x' FROM tablef s WHERE s.atextfield = ru.atextfieldAND
s.boolcol4 IS TRUE )
  ORDER by ru.anothertextfield asc



Sort  (cost=2341.96..2341.97 rows=2 width=146) (actual time=
1118.810..1119.098 rows=228 loops=1)
  Sort Key: ru.anothertextfield
  ->  Nested Loop Left Join  (cost=0.00..2341.95 rows=2 width=146) (actual
time= 0.930..1117.258 rows=228 loops=1)
->  Nested Loop  (cost=0.00..2313.36 rows=2 width=131) (actual time=
0.842..914.554 rows=228 loops=1)
  ->  Nested Loop  (cost=0.00..2296.65 rows=2 width=93) (actual
time= 0.765..901.916 rows=228 loops=1)
->  Nested Loop  (cost=0.00..2281.98 rows=2 width=72)
(actual time=0.690..893.648 rows=228 loops=1)
  ->  Index Scan using i_tablea_atextfield on tablea
ru  (cost= 0.00..2265.28 rows=2 width=12) (actual
time=0.624..881.313rows=228 loops=1)
Index Cond: (atextfield =
'thelookupval'::text)
Filter: ((achar1field <> 'N'::bpchar) AND
(boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))
SubPlan
  ->  Index Scan using tablef_pkey on tablef
s  (cost=0.00..8.34 rows=1 width=0) (actual time=0.016..0.016 rows=1
loops=228)
Index Cond: (atextfield = $1)
Filter: (boolcol4 IS TRUE)
  ->  Bitmap Heap Scan on tablea fru  (cost=
4.61..90.41 rows=1 width=0) (actual time=3.590..3.590 rows=0 loops=243)
Recheck Cond: (atextfield = $0)
Filter: (boolcol3 IS TRUE)
->  Bitmap Index Scan on
i_tablea_atextfield  (cost= 0.00..4.61 rows=22 width=0) (actual time=
0.044..0.044 rows=17 loops=243)
  Index Cond: (atextfield = $0)
  ->  Index Scan using tablec_pkey on tablec z
(cost= 0.00..8.34 rows=1 width=60) (actual time=0.047..0.049 rows=1
loops=228)
Index Cond: (z.atextfield =
ru.anothertextfield)
->  Index Scan using tabled_pkey on tabled p  (cost=
0.00..7.32 rows=1 width=29) (actual time=0.030..0.031 rows=1 loops=228)
  Index Cond: (p.id = z.pid)
  ->  Index Scan using tableb_pkey on tableb u  (cost=
0.00..8.34 rows=1 width=38) (actual time=0.049..0.051 rows=1 loops=228)
Index Cond: (u.atextfield = ru.anothertextfield)
Filter: ((boolcol1 IS TRUE) AND (boolcol2" IS FALSE))"
->  Index Scan using tablee_atextfield_idx on tablee m  (cost=
0.00..14.28 rows=1 width=39) (actual time=0.883..0.883 rows=0 loops=228)
  Index Cond: (u.atextfield = m.atextfield)

Re: [SQL] slow query - only uses indices

2007-12-24 Thread imad 
On Dec 24, 2007 7:46 PM, Marc <[EMAIL PROTECTED]> wrote:
> Hey Folks,
>
> This query is running really slowly.  Sometimes much slower then others.  I
> have a feeling that there may be contention on one of the indices it is
> using.  In the explain plan, it looks like it estimates 2 rows but actually
> finds 228 rows?  Is that really bad?
>

You should do an ANALYZE if actual rows are significantly different.
And they are.

>
>
> Query and explain plan are below.  Seems like it spend the most time doing
> Index Scan using i_tablea_atextfield on tablea ru  (cost=0.00..2265.28
> rows=2 width=12) (actual time=0.624..881.313 rows=228 loops=1)
>
> Any suggestions?
>
> SELECT z.atextfield,
>z.btextfield,
>z.abigintfield,
>p.achar255field, p.ptextfield,
>z.achar1field, u.aboolfield,
>z.textfield1,
>z.achar8field,
>z.achar16field
> FROM tablea ru
> INNER JOIN tableb u ON ( u.atextfield = ru.anothertextfield )
> INNER JOIN tablec z ON u.atextfield = z.atextfield
> INNER JOIN tabled p ON p.id = z.pid
> LEFT JOIN tablee m ON u.atextfield = m.atextfield  AND m.boolcol5
>  WHERE ru.atextfield = 'thelookupval'
>   AND u.boolcol1 IS TRUE
>   AND u.boolcol2 IS FALSE
>   AND ru.achar1field <> 'N'
>   AND ru.boolcol3 IS FALSE
>   AND NOT EXISTS ( SELECT 'x' FROM tablea fru WHERE fru.atextfield =
> ru.anothertextfield AND fru.boolcol3 IS TRUE )
>   AND EXISTS ( SELECT 'x' FROM tablef s WHERE s.atextfield = ru.atextfield
> AND s.boolcol4 IS TRUE )
>   ORDER by ru.anothertextfield asc
>
>
>
> Sort  (cost=2341.96..2341.97 rows=2 width=146) (actual
> time=1118.810..1119.098 rows=228 loops=1)
>   Sort Key: ru.anothertextfield
>   ->  Nested Loop Left Join  (cost=0.00..2341.95 rows=2 width=146) (actual
> time= 0.930..1117.258 rows=228 loops=1)
> ->  Nested Loop  (cost=0.00..2313.36 rows=2 width=131) (actual
> time=0.842..914.554 rows=228 loops=1)
>   ->  Nested Loop  (cost=0.00..2296.65 rows=2 width=93) (actual
> time= 0.765..901.916 rows=228 loops=1)
> ->  Nested Loop  (cost=0.00..2281.98 rows=2 width=72)
> (actual time=0.690..893.648 rows=228 loops=1)
>   ->  Index Scan using i_tablea_atextfield on tablea
> ru  (cost= 0.00..2265.28 rows=2 width=12) (actual time=0.624..881.313
> rows=228 loops=1)
> Index Cond: (atextfield =
> 'thelookupval'::text)
> Filter: ((achar1field <> 'N'::bpchar) AND
> (boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))
> SubPlan
>   ->  Index Scan using tablef_pkey on tablef
> s  (cost=0.00..8.34 rows=1 width=0) (actual time=0.016..0.016 rows=1
> loops=228)
> Index Cond: (atextfield = $1)
> Filter: (boolcol4 IS TRUE)
>   ->  Bitmap Heap Scan on tablea fru
> (cost=4.61..90.41 rows=1 width=0) (actual time=3.590..3.590 rows=0
> loops=243)
> Recheck Cond: (atextfield = $0)
> Filter: (boolcol3 IS TRUE)
> ->  Bitmap Index Scan on
> i_tablea_atextfield  (cost= 0.00..4.61 rows=22 width=0) (actual
> time=0.044..0.044 rows=17 loops=243)
>   Index Cond: (atextfield = $0)
>   ->  Index Scan using tablec_pkey on tablec z
> (cost= 0.00..8.34 rows=1 width=60) (actual time=0.047..0.049 rows=1
> loops=228)
> Index Cond: (z.atextfield =
> ru.anothertextfield)
> ->  Index Scan using tabled_pkey on tabled p  (cost=
> 0.00..7.32 rows=1 width=29) (actual time=0.030..0.031 rows=1 loops=228)
>   Index Cond: (p.id = z.pid)
>   ->  Index Scan using tableb_pkey on tableb u  (cost=
> 0.00..8.34 rows=1 width=38) (actual time=0.049..0.051 rows=1 loops=228)
> Index Cond: (u.atextfield = ru.anothertextfield)
> Filter: ((boolcol1 IS TRUE) AND (boolcol2" IS FALSE))"
> ->  Index Scan using tablee_atextfield_idx on tablee m
> (cost=0.00..14.28 rows=1 width=39) (actual time=0.883..0.883 rows=0
> loops=228)
>   Index Cond: (u.atextfield = m.atextfield)
>
>
>
>


I see a lot of Nested Nested Nested Loops on text fields which means a
lot of string comparisons.
Try using HashJoin here.

ANALYZE your database and explain again. Or try forcing a hashjoin if you still
dont see a difference.


--Imad

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] slow query - only uses indices

2007-12-24 Thread Pavel Stehule 
Hello

there is some differences:

ndex Scan using i_tablea_atextfield on tablea ru  (cost= 0.00..2265.28
rows=2 width=12) (actual time=0.624..881.313 rows=228 loops=1)
Index Cond: (atextfield = 'thelookupval'::text)
Filter: ((achar1field <> 'N'::bpchar)
AND (boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))

try tu increase statistic for column atextfield on table ru, ANALYZE
it again and test it.

Pavel

On 24/12/2007, imad <[EMAIL PROTECTED]> wrote:
> On Dec 24, 2007 7:46 PM, Marc <[EMAIL PROTECTED]> wrote:
> > Hey Folks,
> >
> > This query is running really slowly.  Sometimes much slower then others.  I
> > have a feeling that there may be contention on one of the indices it is
> > using.  In the explain plan, it looks like it estimates 2 rows but actually
> > finds 228 rows?  Is that really bad?
> >
>
> You should do an ANALYZE if actual rows are significantly different.
> And they are.
>
> >
> >
> > Query and explain plan are below.  Seems like it spend the most time doing
> > Index Scan using i_tablea_atextfield on tablea ru  (cost=0.00..2265.28
> > rows=2 width=12) (actual time=0.624..881.313 rows=228 loops=1)
> >
> > Any suggestions?
> >
> > SELECT z.atextfield,
> >z.btextfield,
> >z.abigintfield,
> >p.achar255field, p.ptextfield,
> >z.achar1field, u.aboolfield,
> >z.textfield1,
> >z.achar8field,
> >z.achar16field
> > FROM tablea ru
> > INNER JOIN tableb u ON ( u.atextfield = ru.anothertextfield )
> > INNER JOIN tablec z ON u.atextfield = z.atextfield
> > INNER JOIN tabled p ON p.id = z.pid
> > LEFT JOIN tablee m ON u.atextfield = m.atextfield  AND m.boolcol5
> >  WHERE ru.atextfield = 'thelookupval'
> >   AND u.boolcol1 IS TRUE
> >   AND u.boolcol2 IS FALSE
> >   AND ru.achar1field <> 'N'
> >   AND ru.boolcol3 IS FALSE
> >   AND NOT EXISTS ( SELECT 'x' FROM tablea fru WHERE fru.atextfield =
> > ru.anothertextfield AND fru.boolcol3 IS TRUE )
> >   AND EXISTS ( SELECT 'x' FROM tablef s WHERE s.atextfield = ru.atextfield
> > AND s.boolcol4 IS TRUE )
> >   ORDER by ru.anothertextfield asc
> >
> >
> >
> > Sort  (cost=2341.96..2341.97 rows=2 width=146) (actual
> > time=1118.810..1119.098 rows=228 loops=1)
> >   Sort Key: ru.anothertextfield
> >   ->  Nested Loop Left Join  (cost=0.00..2341.95 rows=2 width=146) (actual
> > time= 0.930..1117.258 rows=228 loops=1)
> > ->  Nested Loop  (cost=0.00..2313.36 rows=2 width=131) (actual
> > time=0.842..914.554 rows=228 loops=1)
> >   ->  Nested Loop  (cost=0.00..2296.65 rows=2 width=93) (actual
> > time= 0.765..901.916 rows=228 loops=1)
> > ->  Nested Loop  (cost=0.00..2281.98 rows=2 width=72)
> > (actual time=0.690..893.648 rows=228 loops=1)
> >   ->  Index Scan using i_tablea_atextfield on tablea
> > ru  (cost= 0.00..2265.28 rows=2 width=12) (actual time=0.624..881.313
> > rows=228 loops=1)
> > Index Cond: (atextfield =
> > 'thelookupval'::text)
> > Filter: ((achar1field <> 'N'::bpchar) AND
> > (boolcol3 IS FALSE) AND (NOT (subplan)) AND (subplan))
> > SubPlan
> >   ->  Index Scan using tablef_pkey on tablef
> > s  (cost=0.00..8.34 rows=1 width=0) (actual time=0.016..0.016 rows=1
> > loops=228)
> > Index Cond: (atextfield = $1)
> > Filter: (boolcol4 IS TRUE)
> >   ->  Bitmap Heap Scan on tablea fru
> > (cost=4.61..90.41 rows=1 width=0) (actual time=3.590..3.590 rows=0
> > loops=243)
> > Recheck Cond: (atextfield = $0)
> > Filter: (boolcol3 IS TRUE)
> > ->  Bitmap Index Scan on
> > i_tablea_atextfield  (cost= 0.00..4.61 rows=22 width=0) (actual
> > time=0.044..0.044 rows=17 loops=243)
> >   Index Cond: (atextfield = $0)
> >   ->  Index Scan using tablec_pkey on tablec z
> > (cost= 0.00..8.34 rows=1 width=60) (actual time=0.047..0.049 rows=1
> > loops=228)
> > Index Cond: (z.atextfield =
> > ru.anothertextfield)
> > ->  Index Scan using tabled_pkey on tabled p  (cost=
> > 0.00..7.32 rows=1 width=29) (actual time=0.030..0.031 rows=1 loops=228)
> >   Index Cond: (p.id = z.pid)
> >   ->  Index Scan using tableb_pkey on tableb u  (cost=
> > 0.00..8.34 rows=1 width=38) (actual time=0.049..0.051 rows=1 loops=228)
> > Index Cond: (u.atextfield = ru.anothertextfield)
> > Filter: ((boolcol1 IS TRUE) AND (boolcol2" IS FALSE))"
> > ->  Index Scan using tablee_atextfield_idx on tablee m
> > (cost=0.00..14.28 rows=1 width=39) (actual

Re: [SQL] Misnamed field in subquery does not cause error when field name exists in parent query

2007-12-24 Thread Rick Innis 


On 22-Dec-07, at 7:14 PM, Michael Glaesemann wrote:



It may be that SQL is doing exactly as it should, since 'id' is in  
scope within the subselect, but  if that's the case it's a nasty  
gotcha.


Yes, and yes.


Ow. Thanks for confirming both my suspicions and my fears :-)

R.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings