I can't find a reasonable explanation for this.
I have a table game, with primary key gameid (an int).
If I use a where involving gameid and <, or >, or <=,
or >=, then I get a sequential scan. If I use =, then
of course I get an Index scan.
More surprising to me is the fact that using BETWEEN,
I get an Index scan!!
The following is a copy-n-paste of the EXPLAIN outputs
(with the useless lines removed):
explain delete from game where gameid = 1000;
Index Scan using game_pkey on game (cost=0.00..3.1 4 rows=1 width=6)
explain delete from game where gameid < 1000;
Seq Scan on game (cost=0.00..477 9.50 rows=200420 width=6)
explain delete from game where gameid between 1000 and 2000;
Index Scan using game_pkey on game (cost=0.00..3.1 5 rows=1 width=6)
How's that possible? Is it purposely done like this, or
is it a bug? (BTW, Postgres version is 7.2.3)
Thanks,
Carlos
--
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org 3  5701 
explain delete from game where gameid = 1000;
Index Scan using game_pkey on game (cost=0.00..3.1 4 rows=1 width=6)
explain delete from game where gameid < 1000;
Seq Scan on game (cost=0.00..477 9.50 rows=200420 width=6)
explain delete from game where gameid between 1000 and 2000;
Index Scan using game_pkey on game (cost=0.00..3.1 5 rows=1 width=6)
How's that possible? Is it purposely done like this, or
is it a bug? (BTW, Postgres version is 7.2.3)
Postgres thinks that for the = line there will only be 1 row so t uses an
index scan. Same thing for the between. However it thinks that there are
200420 rows below 1000 and decides a seq scan would be faster. You can run
EXPLAIN ANALYZE to see if its guesses are correct. You can also try SET
enable_seqscan = FALSE; to see if it is faster doing an index scan. If it
is faster to do an index scan edit your postgres.conf file and lower the
cost for a random tuple, etc.
--- Adam Kavan
--- ak****@cox.net
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
A long time ago, in a galaxy far, far away, mo****@mochima. com (Carlos Moreno) wrote: I can't find a reasonable explanation for this.
I have a table game, with primary key gameid (an int).
If I use a where involving gameid and <, or >, or <=,
or >=, then I get a sequential scan. If I use =, then
of course I get an Index scan.
More surprising to me is the fact that using BETWEEN,
I get an Index scan!!
The following is a copy-n-paste of the EXPLAIN outputs
(with the useless lines removed):
explain delete from game where gameid = 1000;
Index Scan using game_pkey on game (cost=0.00..3.1 4 rows=1 width=6)
explain delete from game where gameid < 1000;
Seq Scan on game (cost=0.00..477 9.50 rows=200420 width=6)
explain delete from game where gameid between 1000 and 2000;
Index Scan using game_pkey on game (cost=0.00..3.1 5 rows=1 width=6)
How's that possible? Is it purposely done like this, or
is it a bug? (BTW, Postgres version is 7.2.3)
It would appear as though the statistics may be a bit stilted such
that the second query is being handled wrongly.
#1 and #3 are pretty clear...
- In #1, it's using the index, correctly estimating that there are
only a few rows with "gameid = 1000"
- In #3, it's using the index, correctly estimating that there are
few rows with gameid between 1000 and 2000.
It seems surprising that the optimizer is estimating that there are
200420 rows with gameid < 1000.
Is it possible that you did an ANALYZE a long while back, back when
you had an enormous number of rows with gameid < 1000?
Try running ANALYZE again on the table, and see if the estimates
change.
--
select 'aa454' || '@' || 'freenet.carlet on.ca'; http://cbbrowne.com/info/advocacy.html
Why isn't phonetic spelled the way it sounds?
On Mon, 15 Sep 2003, Adam Kavan wrote:
explain delete from game where gameid = 1000;
Index Scan using game_pkey on game (cost=0.00..3.1 4 rows=1 width=6)
explain delete from game where gameid < 1000;
Seq Scan on game (cost=0.00..477 9.50 rows=200420 width=6)
explain delete from game where gameid between 1000 and 2000;
Index Scan using game_pkey on game (cost=0.00..3.1 5 rows=1 width=6)
How's that possible? Is it purposely done like this, or
is it a bug? (BTW, Postgres version is 7.2.3)
Postgres thinks that for the = line there will only be 1 row so t uses an
index scan. Same thing for the between. However it thinks that there are
200420 rows below 1000 and decides a seq scan would be faster. You can run
EXPLAIN ANALYZE to see if its guesses are correct. You can also try SET
enable_seqscan = FALSE; to see if it is faster doing an index scan. If it
is faster to do an index scan edit your postgres.conf file and lower the
cost for a random tuple, etc.
Before you do that you might wanna issue this command:
alter table game alter column gameid set statistics 100;
analyze game;
and see what you get.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Dima Tkach |
last post by:
Hi, everybody!
I just ran into a weird problem on 7.3.4.
Here is a simple testcase:
rapidb=# create table nametab (name text);
CREATE TABLE
rapidb=# create index name_idx on nametab(name);
CREATE INDEX
rapidb=# set enable_seqscan=false;
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:
|
by: Gorilla |
last post by:
I bound my package with EXPLAIN(YES), and it's got the following static
SQL in it:
EXEC SQL SELECT CARDF, RECLENGTH
INTO :CARDF,:RECLENGTH
FROM SYSIBM.SYSTABLES
WHERE NAME = :TBNAME
AND CREATOR = :TBCREATOR
The explain shows that it does a *full table scan* on SYSIBM.SYSTABLES!
|
by: Pavel Stehule |
last post by:
Hello,
Pg make query 1. and 2. very fast (use index), but for query 3. dont use
index. I can solve its using select union, but I readed so pg 7.5 don't
problem with OR operator. I use cvs pg. I used vacuum analyze first.
table sp_op_product has 15K rows, sp_op_uct 37K rows;
regards
Pavel Stehule
|
by: Mike Wertheim |
last post by:
Hi,
I'm using PostgreSQL 8.
I have two tables that I am doing a join on, and the join executes very
slowly.
The table called Notification has a text field called NotificationID,
which is its primary key. The Notification table also has an int4 field
called ItemID, and it has an index on the ItemID field. The table
| | |
by: bobby_b_ |
last post by:
I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite...
|
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
on various queries.
|
by: Steffen Stellwag |
last post by:
Truely is often better to scan a table in full passing by an index ,
but if you can force the optimizer to use an index via a hint for
testing and comparing the results.
But the index in the above example is not used , because the hint is
malformed,
if tables in a Select statment are named by aliases you have to
specify the alias name in the hint statment , not the table name
/*+ INDEX (ICWOIMP PK_ICWOIMP) */ change to /*+ INDEX (A...
|
by: Henry J. |
last post by:
I have a composite index on two columns in a table. However, the
index is not used in a query that restricts the 2nd column to a
constant. If both columns are linked with columns in other join
tables, the index will be used.
To illustrate it with an example, I have a query like this:
select s.ticker, p.quantity
from stock s, positions p
where s.type_id = 4
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
