473,382 Members | 1,442 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,382 software developers and data experts.

Avoiding sequential scans with OR join condition

Hello. I have a query like:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);

I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this, I've rewritten the query as:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2

which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.

Is there any way to write the first query such that indexes will be used?

Mike Mascari

---------------------------(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 #1
8 3573
Am Samstag, 16. Oktober 2004 07:23 schrieb Mike Mascari:
Hello. I have a query like:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);

I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this,
Maybe the postgres planner decided to choose a seq scan because the planner
thinks it is faster, and often it is right. Did you vacuum analyze before?

try:
VACCUM ANALYZE;
SET enable_seq_scan to off;
EXPLAIN ANALYZE <your query>
SET enable_seq_scan to on;
EXPLAIN ANALYZE <your query>

you will see why postgres planner did choose a seq scan and if it was right to
do so (but never disable seq scan on production environment, not even for one
query. you do not want it.)

(i hope syntax is correct otherwise consult the manual)
I've rewritten the query as:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2

which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.
this is the reason it seems why postgres choose a seq scan in the first query.
if it has to scan 90% of data anyway, it is faster than doing two index
lookups before.
Is there any way to write the first query such that indexes will be used?
i do not know your db design but it looks queer to me to have a big_table with
two columns y1 and y2 which seems to have the same meaning (some value which
is compared to another value of little_table).

why dont you put just one column "y" in your big_table?

kind regards,
janning
Mike Mascari


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2
If the problem is the sort, use UNION ALL.

As for the query restructuring, I don't know if there is a way of
restructuring the query to do it in a single query. You would be able
to contruct a query plan that would do it, something like:

-> Nested Loop
-> Append
-> Index Scan on big_table.y1
-> Index Scan on big_table.y2
-> Index Scan on little_table

But I have no idea how to get PostgreSQL to produce this...

On Sat, Oct 16, 2004 at 01:23:09AM -0400, Mike Mascari wrote:
Hello. I have a query like:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);

I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this, I've rewritten the query as:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2

which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.

Is there any way to write the first query such that indexes will be used?

Mike Mascari

---------------------------(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
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBcS3mY5Twig3Ge+YRAujSAKC1CPpjQnNccACK9X0hq9 uN5ByLzgCeMs+N
vkNJj2sF+ADGXB8x5UyZ8rA=
=Xcfb
-----END PGP SIGNATURE-----

Nov 23 '05 #3
Mike Mascari <ma*****@mascari.com> writes:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2); Is there any way to write the first query such that indexes will be used?


I'm afraid you're stuck with the UNION workaround. The planner's
treatment of OR indexscans is entirely separate from its treatment of
join indexscans, so it's just not capable of forming the sort of plan
you are envisioning. It'd be nice to improve that someday, but it'd
take either a pile of duplicate code, or a fairly thorough rewrite
of indxpath.c/orindxpath.c.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:

select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null and l2.p_key is not null

I have never tried this in postgresql, but in my experience with
various other DB engines it is a lot faster then using an or in the
join and faster then a union.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

__________________________________________________ ______________________________

Hello. I have a query like:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);

I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this, I've rewritten the query as:

SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2

which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.

Is there any way to write the first query such that indexes will be used?

Mike Mascari

---------------------------(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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5
Sim Zacks wrote:
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:

select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null and l2.p_key is not null

I have never tried this in postgresql, but in my experience with
various other DB engines it is a lot faster then using an or in the
join and faster then a union.


Wow! Thanks! That certainly did the trick.

Mike Mascari

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

Nov 23 '05 #6
Mike Mascari wrote:
Sim Zacks wrote:
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:

select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null and l2.p_key is not null

I have never tried this in postgresql, but in my experience with
various other DB engines it is a lot faster then using an or in the
join and faster then a union.


Wow! Thanks! That certainly did the trick.


I'm thinking that the WHERE clauses condition should read:

WHERE l1.p_pkey is not null OR l2.p_key is not null;

My condition for a given selection of a big_table tuple is that either
y1 or y2 exist as a valid x from little_table. So I think I need an OR
instead of an AND. And AND condition would require that both y1 and y2
for the sample tuple of big_table be a valid x from little_table. Correct?

Mike Mascari
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #7
Mike,

You are probably correct, I was thinking in English, not SQL. That's
what happens when I bang code too early in the morning.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

__________________________________________________ ______________________________

Mike Mascari wrote:
Sim Zacks wrote:
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:

select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null and l2.p_key is not null

I have never tried this in postgresql, but in my experience with
various other DB engines it is a lot faster then using an or in the
join and faster then a union.


Wow! Thanks! That certainly did the trick.


I'm thinking that the WHERE clauses condition should read:

WHERE l1.p_pkey is not null OR l2.p_key is not null;

My condition for a given selection of a big_table tuple is that either
y1 or y2 exist as a valid x from little_table. So I think I need an OR
instead of an AND. And AND condition would require that both y1 and y2
for the sample tuple of big_table be a valid x from little_table. Correct?

Mike Mascari
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #8
On Sun, Oct 17, 2004 at 03:30:32 -0400,
Mike Mascari <ma*****@mascari.com> wrote:

I'm thinking that the WHERE clauses condition should read:

WHERE l1.p_pkey is not null OR l2.p_key is not null;


That seems to make more sense. I was puzzling about that condition myself.
If both keys where not null, there wouldn't even be a need for "left" joins.

Note that the output is different than you were originally getting as
well, since previously rows looked like one big table row combined with
one little table row. Now you are getting one big table row combined with
two little table rows (one of which might be null). This is probably
still faster, but you will need to change how you use the output.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #9

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

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
1
by: Quarco | last post by:
Hey, Suppose you have a table_ex like: id nuber1 chr ( = a or b) 1 1000 a 2 1001 a 3 1002 b 4 1003 a etc..
2
by: Martin | last post by:
I am now working on SQL Server 2000 having had previous experience on a different database. Both of the OUTER JOIN syntaxes is different from what I am used to and I am finding it slightly...
4
by: Omavlana | last post by:
Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from...
8
by: Matt | last post by:
Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and...
3
by: Dam | last post by:
Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
4
by: Jane T | last post by:
I appreciate how difficult it is to resolve a problem without all the information but maybe someone has come across a similar problem. I have an 'extract' table which has 1853 rows when I ask for...
4
by: hgriva1 | last post by:
Hi, Is there a way in which i can create a global temporary table based on join condition eg: scott@ISNS>CREATE GLOBAL TEMPORARY TABLE x 2 AS 3 SELECT deptno,dname 4 FROM( 5...
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...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.