hi,
i have a btree index on col1 in table1. The column has either values
1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
there are about 20 million rows in the table. when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes). why is it taking so long if i have an index on
it? I also tried this with a hash index and it was still slow.
thanx, amir
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly 2  1161 
Did you do an explain? I'd guess the index isn't being used because of
skewed statistics. i have a btree index on col1 in table1. The column has either values
1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
there are about 20 million rows in the table. when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes). why is it taking so long if i have an index on
it? I also tried this with a hash index and it was still slow.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
"Amir Zicherman" <am************ @gmail.com> wrote:
i have a btree index on col1 in table1. The column has either values
1,2,3, or 4. 4 does not appear that much in the table (only 5 times).
there are about 20 million rows in the table. when i do a "select *
from table1 where col1=4" it takes very long time to get back to me
(around 4 minutes). why is it taking so long if i have an index on
it? I also tried this with a hash index and it was still slow.
you need to "analyze table1"
possibly a "select * from table1 where col1=4 order by col1 DESC" could
be faster, as this is the highest value, but i am not sure.
if it is known that the value 4 will always appear MUCH less frequently
then the others, you might try
create index table1_partial on table1(col1) where col1=4;
then the select will definitively be faster for this value.
gnari
---------------------------(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: Jesse Sheidlower |
last post by:
I'm struggling with speed issues on some queries that
I would have expected to be relatively fast. Perhaps
even more frustratingly, when I've tried to break
these down into their components, they still execute
very slowly. I've looked over all the relevant suggestions
for optimization and so forth, and there's nothing I can
tell that I'm missing.
An example of a query is to get all the words (the cg.cw
field) in a particular...
|
by: dixie |
last post by:
I have a table called "tblParticipants" with a field called "ID", which must
be indexed with No duplicates allowed. I am looking for a way of doing in
vba a small if ..then ..else .. routine that looks at that field in that
table and if it is not indexed, indexes it, and if it is indexed, jumps to a
label. I have a query called "qryAddIndex" that will index the field, what
I really need is the vba to test if the field is indexed or not.
...
|
by: MichaelGeier |
last post by:
Hello,
I have a class with three overloaded indexed poperties.
Declaration in *.h
__gc class ByteArrayN : public FArrayN
{
typedef BYTE T;
|
by: Martin Schäfer |
last post by:
Is there any way to find out whether a column that's used in a view is indexed?
I can find out whether a table's column is indexed by going through the pg_class, pg_attribute and pg_index system catalogs. But I haven't been able to extend that method to views.
Can anybody help?
Martin
|
by: Dave Hammond |
last post by:
Hi All,
I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.
My question: would there be any benefit, whatsoever, to having a
| | |
by: liko81 |
last post by:
I have an Invoice class that must know, directly or indirectly, how to
do anything associated with creating, reading, or otherwise processing
an invoice to a customer. It is an uber-DAO object that combines the
business logic and DAO layers. The invoice it handles can have one to
many lines. The most logical implementation is an Invoice object with
an array of InvoiceLine objects. We of course want to prevent Joe
Programmer from directly...
|
by: eriwik |
last post by:
I'm working on an application that performs calculations on triangles
of a 3D-model. As part of those computations I have to calculate a
value for each pair of triangles and the number of triangles can be
quite large (testmodel has 16500 triangles). Each triangle has a
ID-number, however the number-range does not start from 0 but usually
somewhere in the range of 10000-100000 and are not neccesarily
contigious. My problem is to store the...
|
by: Nathan Sokalski |
last post by:
I have created declared a Bitmap using the following statement:
Dim bmp As New Bitmap(100, 100, PixelFormat.Format8bppIndexed)
Because the SetPixel() method is disabled and a Graphics object cannot be
created for indexed PixelFormats, I am not sure how to edit the Bitmap. I am
assuming that there is some class or technique other than the following:
Dim bmpdata As BitmapData = bmp.LockBits(New Rectangle(0, 0, bmp.Width,
bmp.Height),...
|
by: David C. Ullrich |
last post by:
Having a hard time phrasing this in the form
of a question...
The other day I saw a thread where someone asked
about overrideable properties and nobody offered
the advice that properties are Bad. So maybe we've
got over that. I suppose properties could have
Bad consequences if a user doesn't know they exist
and think that a certain property of an object is
just an ordinary attribute. But that applies to
|
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: 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: 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: 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...
|
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: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
