473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Table access method not behaving like Oracle (index vs sequential scan). Examples and stats provided.

I expected Postgresql to use an indexed access method, but in certain cases
it is using a sequential scan. Details are below:

Table:

P1_NRN_ROAD (
sobjid int8 primary key,
v int8 not null,
ord int2 not null)

* The table contains 1.1 million rows.
* Column 'v' exhibits very high selectivity: "select count(*) from (select
distinct v from p1_nrn_road) A" returns 1,051,276.
* The table contains one index: P1_NRN_ROAD_V (v, sobjid) (The index
includes the column sobjid because the query projects this col, and its
inclusion in the index allows it to be serviced without accessing the
underlying table)
* The table was vacuumed and analyzed after the index was created (I even
set the col statistics to 1000)

Now, for the queries:

QUERY 1: select sobjid from p1_nrn_road where v = 2226497481090365991

The plan is "Index scan using p1_nrn_road_v on p1_nrn_road (cost=0.00..6.52
rows=2 width=8)"

The plan was close: only one row is returned. Everything is fine here.
QUERY 2: select sobjid from p1_nrn_road where v = 1

The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)"

The plan is wrong: "select min(v) from p1_nrn_road" returns
2226497481090365991, which indicates that the query will return 0 rows.

I can't understand why a sequential scan is selected for query 2 when the
plan suggests only two rows (high selectivity) are expected.

Oracle doesn't behave like this, and I expect Postgresql to behave similarly
(are my expectations too high?)

Thank you in advance for any assistance you can provide.

Nov 23 '05 #1
3 2021
Kevin Macdonald wrote:

QUERY 2: select sobjid from p1_nrn_road where v = 1

The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)"

The plan is wrong: "select min(v) from p1_nrn_road" returns
2226497481090365991, which indicates that the query will return 0 rows.

Hy Kevin,

please try

select sobjid from p1_nrn_road where v = '1'

(notice quotes)

I'm not sure but i think indexes that consists of more than 2 rows have
to be casted to char in PG.

Daniel.
Nov 23 '05 #2

"Kevin Macdonald" <no****@please.thankyou> writes:
QUERY 2: select sobjid from p1_nrn_road where v = 1

The plan is "Seq Scan on p1_nrn_road (cost=0.00..22158.54 rows=2 width=8)"


Incidentally, you should send the whole plan. In general you should send all
the information you have, not just the parts you think are relevant. The next
line would have clearly shown the problem to someone who knew what to look
for.

try
where v = '1'
or
where v = 1::bigint

The problem is that in 7.4 and previous indexes can't be used for cross-type
comparisons and integer constants are assumed to be integer not bigint type.
If you leave it in quotes then postgres doesn't pick a type until it looks at
what you're comparing it with. Or if you cast it then you force it to be a
bigint=bigint comparison.

7.5 will avoid this problem.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
On Thu, 22 Jul 2004 04:34:26 GMT, "Kevin Macdonald"
<no****@please.thankyou> wrote:
(The index
includes the column sobjid because the query projects this col, and its
inclusion in the index allows it to be serviced without accessing the
underlying table)


No, Postgres always consults the heap to get the desired values. The
most obvious reason is that visibility information is stored in heap
tuples but not in index tuples.

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select...
4
by: ryan | last post by:
Hi Everyone, I'm having trouble convincing myself that Oracle is executing a query of mine as efficiently as possible, and I'm looking for a little guidance. The situation is pretty simple. I...
14
by: Ruth | last post by:
Hi All I am not a DBA, but a unix administrator. After our DBA's upgraded from oracle 8.0.5 to oracle 8.1.7.4 on our test server, we have noticed a big slow down in our application...
5
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. ...
3
by: selma | last post by:
I am puzzled by visual explain for a simple query ( select * from mytable ) chooses an index scan. That index has only a single column and the table has 30 columns not covered by the index. When...
8
by: Michel Esber | last post by:
Hello, DB2 V8 FP 11 running on Linux. Given two tables: T_SW_ID (SW_ID INTEGER, SW_NAME VARCHAR); T_SW (MACHINE_ID varchar, SW_ID DECIMAL (8), VERSION varchar, Product_ID varchar)
6
by: Bob Alston | last post by:
I am looking for others who have built systems to scan documents, index them and then make them accessible from an Access database. My environment is a nonprofit with about 20-25 case workers who...
0
by: Vinod Sadanandan | last post by:
Table Partition Performance analysis ============================================ Collection of Statistics for Cost-Based Optimization/DBMS_STATS vs. ANALYZE The cost-based approach relies on...
2
by: BD | last post by:
Hi, all. My background is more Oracle than db2. My skills at SQL tuning are quite limited. I'm running 8.2 on Windows. I'm tasked with some SQL optimization, and am doing some explain plans...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.