Re: [HACKERS] index theory

2002-10-16 Thread Hannu Krosing 

Karel Zak kirjutas K, 16.10.2002 kell 15:19:
> 
>  Hi,
> 
>  I have SQL query:
> 
>  SELECT * FROM ii WHERE i1='a' AND i2='b';
> 
>  There're indexes on i1 and i2. I know best solution is use one
>  index on both (i1, i2).
> 
>  The EXPLAIN command show that optimalizer wants to use one index:
> 
> test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b';
>QUERY PLAN
> -
>  Index Scan using i1 on ii  (cost=0.00..4.83 rows=1 width=24)
>Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))
> 
> 
>  It's right and I undererstand why not use both indexes. But I talked
> about it with one Oracle user and he said me Oracle knows use both indexes 
> and results from both index scans are mergeted to final result -- this is maybe 
> used if full access to table (too big rows?) is more expensive than 2x index 
> scan and final merge. Is in PG possible something like this?

There has been some talk about using bitmaps generated from indexes as
an intermediate step.

--
Hannu

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] index theory

2002-10-16 Thread Karel Zak 

On Wed, Oct 16, 2002 at 09:25:37AM -0400, Rod Taylor wrote:
> On Wed, 2002-10-16 at 09:19, Karel Zak wrote:
> > 
> >  Hi,
> > 
> >  I have SQL query:
> > 
> >  SELECT * FROM ii WHERE i1='a' AND i2='b';
> > 
> >  There're indexes on i1 and i2. I know best solution is use one
> >  index on both (i1, i2).
> > 
> >  The EXPLAIN command show that optimalizer wants to use one index:
> > 
> > test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b';
> >QUERY PLAN
> > -
> >  Index Scan using i1 on ii  (cost=0.00..4.83 rows=1 width=24)
> >Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))
> 
> I think you typo'd.  i1='a' AND i1='b' turns into 'a' = 'b' which
> certainly isn't true in any alphabets I know of.

 Oh... sorry, right is:

test=# explain SELECT * FROM ii WHERE i1='a' AND i2='b';
  QUERY PLAN   
---
 Index Scan using i2 on ii  (cost=0.00..17.08 rows=1 width=24)
   Index Cond: (i2 = 'b'::character varying)
   Filter: (i1 = 'a'::character varying)

 The query is not important ... it's dummy example only. I think about two 
indexes on one table for access to table.

Karel

-- 
 Karel Zak  <[EMAIL PROTECTED]>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] index theory

2002-10-16 Thread Rod Taylor 

On Wed, 2002-10-16 at 09:19, Karel Zak wrote:
> 
>  Hi,
> 
>  I have SQL query:
> 
>  SELECT * FROM ii WHERE i1='a' AND i2='b';
> 
>  There're indexes on i1 and i2. I know best solution is use one
>  index on both (i1, i2).
> 
>  The EXPLAIN command show that optimalizer wants to use one index:
> 
> test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b';
>QUERY PLAN
> -
>  Index Scan using i1 on ii  (cost=0.00..4.83 rows=1 width=24)
>Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))

I think you typo'd.  i1='a' AND i1='b' turns into 'a' = 'b' which
certainly isn't true in any alphabets I know of.

-- 
  Rod Taylor


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

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] index theory

2002-10-16 Thread Karel Zak 


 Hi,

 I have SQL query:

 SELECT * FROM ii WHERE i1='a' AND i2='b';

 There're indexes on i1 and i2. I know best solution is use one
 index on both (i1, i2).

 The EXPLAIN command show that optimalizer wants to use one index:

test=# explain SELECT * FROM ii WHERE i1='a' AND i1='b';
   QUERY PLAN
-
 Index Scan using i1 on ii  (cost=0.00..4.83 rows=1 width=24)
   Index Cond: ((i1 = 'a'::character varying) AND (i1 = 'b'::character varying))


 It's right and I undererstand why not use both indexes. But I talked
about it with one Oracle user and he said me Oracle knows use both indexes 
and results from both index scans are mergeted to final result -- this is maybe 
used if full access to table (too big rows?) is more expensive than 2x index 
scan and final merge. Is in PG possible something like this? And within 
query/table? I know about it in JOIN (and subselect maybe) only, but in 
the "standard" WHERE?

test=# explain SELECT * FROM ii a JOIN ii b ON a.i1=b.i2;
QUERY PLAN
--
 Merge Join  (cost=0.00..171.50 rows=5000 width=48)
   Merge Cond: ("outer".i1 = "inner".i2)
   ->  Index Scan using i1 on ii a  (cost=0.00..52.00 rows=1000 width=24)
   ->  Index Scan using i2 on ii b  (cost=0.00..52.00 rows=1000 width=24)


 Thanks,

Karel
 
-- 
 Karel Zak  <[EMAIL PROTECTED]>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]