Mladen Gogala wrote:
> Yes, Oracle can be forced into doing a sequential scan and it is
> actually faster than an index scan:
And PostgreSQL can be coerced to use an indexed scan. Its plans are
cost-based, with user configurable cost factors; so if you tell it
that seq_page_cost and random_
On 1/27/2011 4:25 PM, Scott Marlowe wrote:
On Oracle? Then how can it get the values it needs without having to
hit the data store?
It can't. It does hit the data store.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
--
Sent via pgsql-perform
On Thu, Jan 27, 2011 at 2:32 PM, Igor Neyman wrote:
>> On Oracle? Then how can it get the values it needs without
>> having to hit the data store?
>
> It doesn't.
> It does "INDEX UNIQUE SCAN" and then "TABLE ACCESS BY INDEX ROWID".
Ahhh, ok. I thought Oracle used covering indexes by default.
To: Igor Neyman
> >> Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall;
> >> pgsql-performance@postgresql.org
> >> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >>
> >> On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman
>
On 1/27/2011 4:20 PM, Kenneth Marshall wrote:
Interesting. Can you force it to use a Seqential Scan and if so, how
does that affect the timing? i.e. Is the index scan actually faster?
Cheers,
Ken
Yes, Oracle can be forced into doing a sequential scan and it is
actually faster than an index sca
th Marshall;
>> pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
>>
>> On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman
>> wrote:
>> >
>> >
>> >> -Original Message-
>> >> From: Scott
Wilson; Kenneth Marshall;
> > pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >
> > On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
> > wrote:
> > > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
&
t;> To: Mladen Gogala
> >> Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
> >> pgsql-performance@postgresql.org
> >> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >>
> >> On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
&g
nneth Marshall;
>> pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
>>
>> On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
>> wrote:
>> > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
>> >>
>> >> On
> -Original Message-
> From: Scott Marlowe [mailto:scott.marl...@gmail.com]
> Sent: Thursday, January 27, 2011 3:59 PM
> To: Mladen Gogala
> Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
> pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Po
On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
wrote:
> On 1/27/2011 3:37 PM, Scott Marlowe wrote:
>>
>> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
>> wrote:
>>>
>>> There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
>>
>> That's because Oracle has covering indexes.
>>
> I am not
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
wrote:
There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle has covering indexes.
I am not sure what you mean by "covering indexes" but I hope that for
the larger table I
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
wrote:
> There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle has covering indexes.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgr
On 1/27/2011 3:10 PM, Igor Neyman wrote:
Mladen,
I don't think, this is exclusive Postgres feature.
I'm pretty sure, Oracle optimizer will do "TABLE ACCESS (FULL)" instead
of using index on 14-row table either.
Regards,
Igor Neyman
Well, lets' see:
SQL> select * from v$version;
BANNER
> -Original Message-
> From: Mladen Gogala [mailto:mladen.gog...@vmsinfo.com]
> Sent: Thursday, January 27, 2011 12:00 PM
> To: Tom Lane
> Cc: David Wilson; Kenneth Marshall; pgsql-performance@postgresql.org
> Subject: Re: Postgres 9.0 has a bias against indexes
>
> On 1/27/2011 11:40
On 1/27/2011 11:40 AM, Tom Lane wrote:
It is worth noting that EXPLAIN results should not be extrapolated
to situations other than the one you are actually testing; for
example, results on a toy-sized table cannot be assumed to apply to
large tables.
Well, that's precisely what I t
David Wilson writes:
> You're still using a 14 row table, though.
Exactly. Please note what it says in the fine manual:
It is worth noting that EXPLAIN results should not be extrapolated
to situations other than the one you are actually testing; for
example, results on a toy-sized t
On Thu, Jan 27, 2011 at 10:56 AM, Mladen Gogala
wrote:
> I even tried with an almost equivalent outer join:
>
> explain analyze select e1.empno,e1.ename,e2.empno,e2.ename
> from emp e1 left outer join emp e2 on (e1.mgr=e2.empno);
> QUERY PLAN
>
>
>
On 1/27/2011 10:51 AM, J Sisson wrote:
Also, if random_page_cost is set to default (4.0), the planner will
tend towards sequential scans.
scott=> show random_page_cost;
random_page_cost
--
1
(1 row)
scott=> show seq_page_cost;
seq_page_cost
---
2
(1 row)
--
Mla
On 1/27/2011 10:45 AM, Kenneth Marshall wrote:
PostgreSQL will only use an index if the planner thinks that it
will be faster than the alternative, a sequential scan in this case.
For 14 rows, a sequential scan is 1 read and should actually be
faster than the index. Did you try the query using EX
Odds are that a table of 14 rows will more likely be cached in RAM
than a table of 14 million rows. PostgreSQL would certainly be more
"openminded" to using an index if chances are low that the table is
cached. If the table *is* cached, though, what point would there be
in reading an index?
Also
Mladen Gogala wrote:
> The optimizer will not use index, not even when I turn off both
> hash and merge joins. This is not particularly important for a
> table with 14 rows, but for a larger table, this is a problem.
If it still does that with a larger table. Do you have an example
of that?
On Thu, Jan 27, 2011 at 10:41:08AM -0500, Mladen Gogala wrote:
> I have a table EMP, with 14 rows and a description like this:
> scott=> \d+ emp
> Table "public.emp"
> Column |Type | Modifiers | Storage |
> Description
> --+-
23 matches
Mail list logo
