I have a table EMP, with 14 rows and a description like this:
scott= \d+ emp
Table public.emp
Column |Type | Modifiers | Storage |
Description
--+-+---+--+-
empno|
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
Mladen Gogala mladen.gog...@vmsinfo.com 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
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,
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
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)
--
On Thu, Jan 27, 2011 at 10:56 AM, Mladen Gogala
mladen.gog...@vmsinfo.comwrote:
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);
David Wilson david.t.wil...@gmail.com 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,
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
-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 AM, Tom
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
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com 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
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com 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
On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
There is INDEX UNIQUE SCAN PK_EMP. Oracle will use an index.
That's because Oracle
-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] Postgres 9.0 has a bias against
@postgresql.org
Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote:
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
mladen.gog...@vmsinfo.com wrote
;
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
mladen.gog...@vmsinfo.com wrote:
On 1/27/2011 3:37 PM, Scott Marlowe wrote:
On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
@postgresql.org
Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman
iney...@perceptron.com wrote:
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday, January 27, 2011 3:59 PM
To: Mladen
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
;
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
iney...@perceptron.com
wrote:
-Original Message-
From: Scott Marlowe [mailto:scott.marl...@gmail.com]
Sent: Thursday
On Thu, Jan 27, 2011 at 2:32 PM, Igor Neyman iney...@perceptron.com 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
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
Mladen Gogala mladen.gog...@vmsinfo.com 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
23 matches
Mail list logo
