why is this index not being used?

mike

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfShare e = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee) .
Item has index item_ix_item_3_ idx on (DomainID, ItemID).
Sharing has index sharing_ix_shar ing_1_idx on (UserIDOfSharee , ItemID).

Explain says:
Hash Join (cost=25526.26. .31797.78 rows=6105 width=23)
Hash Cond: ("outer".ite mid = "inner".ite mid)
-> Seq Scan on document d (cost=0.00..562 9.14 rows=113214 width=11)
-> Hash (cost=25502.60. .25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..2 5502.60 rows=9465 width=12)
Merge Cond: ("outer".domain id = "inner".ite mid)
-> Index Scan using item_ix_item_3_ idx on item i
(cost=0.00..246 34.71 rows=175519 width=8)
-> Sort (cost=310.16..3 10.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_shar ing_1_idx on
sharing s (cost=0.00..305 .88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use Document's
primary key?
Thanks!
Mike

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

Nov 23 '05 #1

Subscribe Reply

1474

mike

BTW -- We vacuum nightly, and running vacuum analyze doesn't make a
difference.

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering
why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfShare e = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee) .
Item has index item_ix_item_3_ idx on (DomainID, ItemID).
Sharing has index sharing_ix_shar ing_1_idx on (UserIDOfSharee , ItemID).

Explain says:
Hash Join (cost=25526.26. .31797.78 rows=6105 width=23)
Hash Cond: ("outer".ite mid = "inner".ite mid)
-> Seq Scan on document d (cost=0.00..562 9.14 rows=113214 width=11)
-> Hash (cost=25502.60. .25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..2 5502.60 rows=9465 width=12)
Merge Cond: ("outer".domain id = "inner".ite mid)
-> Index Scan using item_ix_item_3_ idx on item i
(cost=0.00..246 34.71 rows=175519 width=8)
-> Sort (cost=310.16..3 10.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_shar ing_1_idx on
sharing s (cost=0.00..305 .88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use
Document's primary key?
Thanks!
Mike

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

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

Nov 23 '05 #2

Gavin M. Roy

You can always try and force it by doing

"set enable_seqscan= 0"

I'd try explain analyze on the query with it on (=1) and off (=0) and
see why the planner likes seqscan better.

Gavin

mi**@linkify.co m wrote:

BTW -- We vacuum nightly, and running vacuum analyze doesn't make a
difference.

Hey all,

I'm using PostgreSQL 7.3.4.

I have a query that isn't using a particular index, and I'm wondering
why.

The query is:
select i.ItemID, d.Extension from ITEM i, SHARING s, DOCUMENT d where
i.ItemID = d.ItemID AND s.ItemID = i.DomainID AND s.UserIDOfShare e = 12

Item's primary key is ItemID.
Document's primary key is ItemID.
Sharing's primary key is (ItemID, UserIDOfSharee) .
Item has index item_ix_item_3_ idx on (DomainID, ItemID).
Sharing has index sharing_ix_shar ing_1_idx on (UserIDOfSharee , ItemID).

Explain says:
Hash Join (cost=25526.26. .31797.78 rows=6105 width=23)
Hash Cond: ("outer".ite mid = "inner".ite mid)
-> Seq Scan on document d (cost=0.00..562 9.14 rows=113214 width=11)
-> Hash (cost=25502.60. .25502.60 rows=9465 width=12)
-> Merge Join (cost=310.16..2 5502.60 rows=9465 width=12)
Merge Cond: ("outer".domain id = "inner".ite mid)
-> Index Scan using item_ix_item_3_ idx on item i
(cost=0.00..2 4634.71 rows=175519 width=8)
-> Sort (cost=310.16..3 10.47 rows=123 width=4)
Sort Key: s.itemid
-> Index Scan using sharing_ix_shar ing_1_idx on
sharing s (cost=0.00..305 .88 rows=123 width=4)
Index Cond: (useridofsharee = 12)
Why is there a Seq Scan on Document? How can I get it to use
Document's primary key?
Thanks!
Mike

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

Similar topics

5738

index statistics

by: Heiko | last post by:

Hello, is there any way (v$-view) to get informaion about how often an index hast been used since of starting the Database? Thanks for help Heiko

Oracle Database

3933

Is KEY and INDEX the same thing?

by: Phil Latio | last post by:

I am following a book on PHP and MySQL and have come across the below SQL statement. CREATE TABLE users ( user_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, first_name VARCHAR(15) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(40) NULL, password VARCHAR(16) NOT NULL,

MySQL Database

4840

Index usage on db2 v7 for os390

by: Andr? Queiroz | last post by:

Hi, I have a table with 10M records and col A has a index created on it. The data on that table has the same value for col A on all 10M records. After that I insert diferent values for that column but my queries do not use the index I created for that column. Is there any way I can force the usage of the index or to ommit a value on the index creation, like 0 (zeroes) or spaces? Thanks in advance, André Queiroz

DB2 Database

5878

Which tablespce is used when running ALTER TABLE & CREATE INDEX

by: maricel | last post by:

Could someone confirm which tablespace is being used when running ALTER & CREATE INDEX. Is it the tempspace or the tablespace where the table resides? Many thanks, maricel

DB2 Database

1500

Index question

by: usenet | last post by:

Hi. If I have an index (or primary key) on 3 columns, will a subset of these columns automatically be indexed also? For example: CREATE INDEX idx ON tbl(cola, colb, colc); Will: SELECT cold FROM tbl WHERE cola = 1 AND colb = 2

DB2 Database

2348

track positions in arrays= index variables || pointers to elements?

by: Deniz Bahar | last post by:

Hello all, Often times programs in C have arrays used as buffers and shared among different sections of code. The need arises to have position indicators to point to different parts of an array (example: point to top of stack). Before I even got K&R2 I used to just define extra pointers to types equal to the element type of the arrays to act as indicators. Now flipping through K&R2, I see they use int variables to act as "offsets." ...

C / C++

122

5542

"index" method only for mutable sequences??

by: C.L. | last post by:

I was looking for a function or method that would return the index to the first matching element in a list. Coming from a C++ STL background, I thought it might be called "find". My first stop was the Sequence Types page of the Library Reference (http://docs.python.org/lib/typesseq.html); it wasn't there. A search of the Library Reference's index seemed to confirm that the function did not exist. A little later I realized it might be called...

Python

1725

Do this as a list comprehension?

by: John Salerno | last post by:

Is it possible to write a list comprehension for this so as to produce a list of two-item tuples? base_scores = range(8, 19) score_costs = print zip(base_scores, score_costs) I can't think of how the structure of the list comprehension would work in this case, because it seems to require iteration over two separate sequences to produce each item in the tuple.

Python

3943

why this 2-column index not used

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

DB2 Database

3162

Re: MDC vs. cluster index: which to use?

by: Ian | last post by:

Henry J. wrote: MDC *guarantees* clustering, whereas a table with a clustering index will eventually require maintenance (a.k.a. reorg) to maintain the cluster ratio. That's not to say that a clustering index isn't still valuable (especially for high cardinality columns that aren't a reasonable candidate as an MDC dimension).

DB2 Database

9690

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10504

Problem With Comparison Operator <=> in G++

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...

C / C++

10274

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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...

Online Marketing

10251

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10033

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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...

General

9085

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7576

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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...

Microsoft Access / VBA

5606

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3764

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP