Hi:
I must have missed something, but how is it possible that a join on
tables A and B is faster (a lot faster) than a query to one of the
tables with the same conditions?
The problem seems to be with the query plan, in the case os a query to
table_a only, the planner executes a "Seq Scan", in the case of a join,
an "Index Scan". table_a has about 4M records, so the difference is
quite noticeable.
explain
select * from table_a where field_1=1 and field_2='200308 08' and
field_3='963782 342';
NOTICE: QUERY PLAN:
Seq Scan on table_a (cost=0.00..373 661.73 rows=12 width=227)
EXPLAIN
explain
select * FROM table_b, table_a
WHERE
table_b.field_1 = table_a.field_1
AND table_b.field_3 = table_a.field_3
AND table_b.field_3 in ('963782342')
AND table_a.field_2 = '20030808'
;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..317 .07 rows=3 width=351)
-> Seq Scan on table_b (cost=0.00..308 .80 rows=1 width=124)
-> Index Scan using table_a_i01 on table_a (cost=0.00..8.2 4 rows=2
width=227)
EXPLAIN
Index on table_a is defined on field_1, field_2 and field_3.
Thanks a lot for any help.
Ruben.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 2 3474
On Mon, Aug 11, 2003 at 01:48:21PM +0200, ruben wrote: Hi: I must have missed something, but how is it possible that a join on tables A and B is faster (a lot faster) than a query to one of the tables with the same conditions? The problem seems to be with the query plan, in the case os a query to table_a only, the planner executes a "Seq Scan", in the case of a join, an "Index Scan". table_a has about 4M records, so the difference is quite noticeable. explain select * from table_a where field_1=1 and field_2='200308 08' and field_3='963782 342'; NOTICE: QUERY PLAN: Seq Scan on table_a (cost=0.00..373 661.73 rows=12 width=227) EXPLAIN
Let me guess, field_1 is not an int4 and since you didn't quote the constant
"1", it can't use the index.
The second query has matching types, so can you the index.
Hope this helps,
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ "All that is needed for the forces of evil to triumph is for enough good men to do nothing." - Edmond Burke "The penalty good people pay for not being interested in politics is to be governed by people worse than themselves." - Plato
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iD8DBQE/N5KSY5Twig3Ge+Y RAnUzAJkBlw08Zu YVHXVZe32ATOUEH qG+xwCeNPd5
95q/l+Snbm/nGZSKM/H2L9Y=
=cGLQ
-----END PGP SIGNATURE-----
Hi Martijn:
Thanks for your answer, I really missed something ;-)
Kind regards, Ruben.
Martijn van Oosterhout wrote: On Mon, Aug 11, 2003 at 01:48:21PM +0200, ruben wrote:
Hi:
I must have missed something, but how is it possible that a join on tables A and B is faster (a lot faster) than a query to one of the tables with the same conditions?
The problem seems to be with the query plan, in the case os a query to table_a only, the planner executes a "Seq Scan", in the case of a join, an "Index Scan". table_a has about 4M records, so the difference is quite noticeable.
explain select * from table_a where field_1=1 and field_2='200308 08' and field_3='9637 82342'; NOTICE: QUERY PLAN:
Seq Scan on table_a (cost=0.00..373 661.73 rows=12 width=227)
EXPLAIN
Let me guess, field_1 is not an int4 and since you didn't quote the constant "1", it can't use the index.
The second query has matching types, so can you the index.
Hope this helps,
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Soefara |
last post by:
Dear Sirs,
I am experiencing strange results when trying to optimize a LEFT JOIN
on 3 tables using MySQL.
Given 3 tables A, B, C such as the following:
create table A (
uniqueId int not null default 0 auto_increment,
a1 varchar(64) not null default '',
|
by: Ike |
last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL
4.0.20-standard. Thus, in trying to expedite the query, I am trying to set
indexes in my tables.
My query requires four inner joins, as follows :
SELECT DISTINCT
upcards.id,statuskey.status,upcards.firstname,upcards.lastname,originkey.ori
gin,associatekey.username,associatekey2.username,upcards.deleted
FROM upcards,status,origins,associates
INNER JOIN status...
|
by: Paul Bramscher |
last post by:
Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes in trees which form parent-child relationships, sort of like
newsgroups. For example, the parent_id field points to another element.
Indent_level is there for denormalization purposes, to avoid costly
recursive issues in querying. The...
|
by: Anthony Robinson |
last post by:
I was actually just wondering if someone could possibly take a look
and tell me what I may be doing wrong in this query? I keep getting
ambiguous column errors and have no idea why...?
Thanks in advance!!!
SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
AIM.AIMRETRIEVAL.DESCRIPTION,
AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
AIM.ARCHIVERETRIEVAL.STATUSID,
|
by: dmonroe |
last post by:
hi group --
Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:
tblEmployees
empId -- EmpName -- EmpRole -- EmpManager
-------....------------.... ---------....---------------
1........ dan yella..........1..........2
| |
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(combination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
|
by: Dave |
last post by:
Guys I am really stuck on this one. Any help or suggestions would be
appreciated.
We have a large table which seemed to just hit some kind of threshold.
They query is somewhat responsive when there are NO indexes on the
table. However, when we index email the query takes forever.
FACTS
- The problem is very "data specific". I can not recreate the
|
by: Chamnap |
last post by:
Hello, everyone
I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
Thanks
Chamnap
|
by: Vivienne |
last post by:
Hi there
This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple and a real version with the hope someone can help me.
Any help would be much appreciated - I would also be happy to pay someone to actually do it if it takes time to work out as I know that its hard when all your help is free :)...
|
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...
|
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: 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,...
|
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: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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: 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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |