f



Why index used/not used

  Hello.

Explain.
  I have table "traf_raw" contains field "sip_id" (integer). This field 
indexed with "CREATE INDEX traf_raw_sip ON traf_raw (sip_id)".

Question.
  When I try to get different rows postgres use index with one "sip_id" 
and not use index with another "sip_id". I don't understand why it is 
happen, but with more complex queries Seq Scan is so slowly.

Example.
  With "sip_id='19'" there many rows in table, with "sip_id='29'" there 
is no rows.

cnupm=> ANALYZE traf_raw;
ANALYZE
cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='19' LIMIT 
10 OFFSET 100000;
                                                      QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------
  Limit  (cost=5230.95..5230.99 rows=1 width=56) (actual 
time=2505.89..2505.89 rows=0 loops=1)
    ->  Seq Scan on traf_raw  (cost=0.00..5230.99 rows=10808 width=56) 
(actual time=0.04..2490.02 rows=10977 loops=1)
          Filter: (sip_id = 19)
  Total runtime: 2505.95 msec
(4 rows)

cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='29' LIMIT 
10 OFFSET 100000;
                                                            QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=391.39..392.70 rows=1 width=56) (actual time=43.08..43.08 
rows=0 loops=1)
    ->  Index Scan using traf_raw_sip on traf_raw  (cost=0.00..392.70 
rows=99 width=56) (actual time=43.07..43.07 rows=0 loops=1)
          Index Cond: (sip_id = 29)
  Total runtime: 43.16 msec
(4 rows)

-- 
engineer

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

0
engineer
7/21/2004 5:00:06 AM
comp.postgresql.general 5128 articles. 0 followers. Post Follow

3 Replies
1705 Views

Similar Articles

[PageSpeed] 40

hello,

Try reading through the pgsql-performance mailing list.  Generally the database needs to be vacuumed and analyzed to update the stats usually for the planner to make the correct choices.

Mike

On Wed, Jul 21, 2004 at 11:00:06AM +0600, Anton Maksimenkov wrote:
>  Hello.
> 
> Explain.
>  I have table "traf_raw" contains field "sip_id" (integer). This field 
> indexed with "CREATE INDEX traf_raw_sip ON traf_raw (sip_id)".
> 
> Question.
>  When I try to get different rows postgres use index with one "sip_id" 
> and not use index with another "sip_id". I don't understand why it is 
> happen, but with more complex queries Seq Scan is so slowly.
> 
> Example.
>  With "sip_id='19'" there many rows in table, with "sip_id='29'" there 
> is no rows.
> 
> cnupm=> ANALYZE traf_raw;
> ANALYZE
> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='19' LIMIT 
> 10 OFFSET 100000;
>                                                      QUERY PLAN 
> 
> ---------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=5230.95..5230.99 rows=1 width=56) (actual 
> time=2505.89..2505.89 rows=0 loops=1)
>    ->  Seq Scan on traf_raw  (cost=0.00..5230.99 rows=10808 width=56) 
> (actual time=0.04..2490.02 rows=10977 loops=1)
>          Filter: (sip_id = 19)
>  Total runtime: 2505.95 msec
> (4 rows)
> 
> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='29' LIMIT 
> 10 OFFSET 100000;
>                                                            QUERY PLAN 
> 
> ---------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=391.39..392.70 rows=1 width=56) (actual time=43.08..43.08 
> rows=0 loops=1)
>    ->  Index Scan using traf_raw_sip on traf_raw  (cost=0.00..392.70 
> rows=99 width=56) (actual time=43.07..43.07 rows=0 loops=1)
>          Index Cond: (sip_id = 29)
>  Total runtime: 43.16 msec
> (4 rows)
> 
> -- 
> engineer
> 
> ---------------------------(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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

0
mike
7/21/2004 5:37:13 AM
On Tue, 2004-07-20 at 23:00, Anton Maksimenkov wrote:
>   Hello.
> 
> Explain.
>   I have table "traf_raw" contains field "sip_id" (integer). This field 
> indexed with "CREATE INDEX traf_raw_sip ON traf_raw (sip_id)".
> 
> Question.
>   When I try to get different rows postgres use index with one "sip_id" 
> and not use index with another "sip_id". I don't understand why it is 
> happen, but with more complex queries Seq Scan is so slowly.
> 
> Example.
>   With "sip_id='19'" there many rows in table, with "sip_id='29'" there 
> is no rows.
> 
> cnupm=> ANALYZE traf_raw;
> ANALYZE
> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='19' LIMIT 
> 10 OFFSET 100000;
>                                                       QUERY PLAN 
> 
> ---------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=5230.95..5230.99 rows=1 width=56) (actual 
> time=2505.89..2505.89 rows=0 loops=1)
>     ->  Seq Scan on traf_raw  (cost=0.00..5230.99 rows=10808 width=56) 
> (actual time=0.04..2490.02 rows=10977 loops=1)
>           Filter: (sip_id = 19)
>   Total runtime: 2505.95 msec
> (4 rows)

Note that in this instance, your query is returning >10k rows.
While in this example:

> cnupm=> EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='29' LIMIT 
> 10 OFFSET 100000;
>                                                             QUERY PLAN 
> 
> ---------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=391.39..392.70 rows=1 width=56) (actual time=43.08..43.08 
> rows=0 loops=1)
>     ->  Index Scan using traf_raw_sip on traf_raw  (cost=0.00..392.70 
> rows=99 width=56) (actual time=43.07..43.07 rows=0 loops=1)
>           Index Cond: (sip_id = 29)
>   Total runtime: 43.16 msec
> (4 rows)

you are only returning 0 rows.

PostgreSQL uses a cost based planner.  So, in the first instance, it
thought it was returning enough rows to justify grabbing all the rows
first.

There are several settings that tune the planner to tell it when to
switch from an index scan to a seq scan.  random_page_cost is the most
commonly adusted one.  Try dropping it from the default of 4 to
something around 1.4 to 2.0 or so.  

Also, you can force the planner to not use seq scans unless it has to by
setting enable_seqscan to off:

set enable_seqscan = off;

and run the query again.  Test several of your queries and find out
where the turning point is, and then adjust the random_page_cost to
cause it to switch at about the right time.


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

0
smarlowe
7/21/2004 8:14:49 AM
Scott Marlowe wrote:

> There are several settings that tune the planner to tell it when to
> switch from an index scan to a seq scan.  random_page_cost is the most
> commonly adusted one.  Try dropping it from the default of 4 to
> something around 1.4 to 2.0 or so.  
....
> and run the query again.  Test several of your queries and find out
> where the turning point is, and then adjust the random_page_cost to
> cause it to switch at about the right time.

  Thank's for explain. With "random_page_cost = 2.0" situation is much 
better. I will follow you tips.

EXPLAIN ANALYZE SELECT * FROM traf_raw WHERE sip_id='19' LIMIT 10 OFFSET 
100000;
....
  Limit  (cost=5042.22..5042.56 rows=1 width=56) (actual 
time=110.32..110.32 rows=0 loops=1)
    ->  Index Scan using traf_raw_sip on traf_raw  (cost=0.00..5042.56 
rows=11020 width=56) (actual time=0.30..95.95 rows=10977 loops=1)
          Index Cond: (sip_id = 19)
  Total runtime: 110.52 msec

-- 
engineer

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

0
engineer
7/21/2004 8:20:44 AM
Reply:

Similar Artilces:

Using where; Using index vs Using where
Hello, What are the difference between the following statements, which one is better? mysql> desc SELECT COUNT(*) as `cnt` FROM vote WHERE `ip` = INET_ATON('127.0.0.1') AND (`addtime` > NOW() - INTERVAL 1 DAY) ; +----+-------------+-------+-------+---------------+--------------- +---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------- +---------+------+------+--------------------------+ | 1 | S...

When to use postgreSQL , when to use other
We are using Oracle and mySQL for web-applications I'm planing to use postgresql also. Maybe we will also use Cach`e . Does anyone using an other database-engines than postgresql also? What are your decision criterias to choose which db-engine for a certain project? e.g. less/more than x records in database , less/more than x select/DML-Staements per second reliablity, backup, replication... ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's ...

Using using
I want to plot column 2 against col. 1, but subtract a constant from col. 2. I tried, in a test script, plot 'test.dat' using 1:($2 + 1) w p pt 7 but gnuplot complains Warning: empty y range [1:1], adjusting to [0.99:1.01] although the data file contains 1 1 2 2 3 3 4 4 5 5 What am I doing wrong? I read this in the manual: plot 'file' using 1:($2+$3) "%1f,%1f,%1f" which led me to use the above. I assume the format stuff is not needed. If I am doing this wrong, how do I modify a given column to be plotted arithmetically? -- Dieter...

when to use == and when to use is
I know this question has been answered: http://stackoverflow.com/questions/6570371/when-to-use-and-when-to-use-is , but I still have doubts. Consider the following code: class A: def __init__(self, a): self._a = a #def __eq__(self, other): # return self._a != other._a obj_0 = A(0) obj_1 = A(1) obj_2 = A(2) obj = obj_1 if obj == obj_0: print(0) elif obj == obj_1: print(1) elif obj == obj_2: print(2) if obj is obj_0: print(0) elif obj is obj_1: print(1) elif obj is obj_2: print(2) Both if statements wo...

when to use ; and when to use ,
Any statements like printf("abc"); scanf("%d",&x); a=20; can be replaced by printf("abc"), scanf("%d",&x), a=20; So when should we use ; and when sould we use , In article <1181971596.941107.318810@o11g2000prd.googlegroups.com>, Ravi <ra.ravi.rav@gmail.com> wrote: >Any statements like > printf("abc"); > scanf("%d",&x); > a=20; >can be replaced by > printf("abc"), scanf("%d",&x), a=20; > >So when should we use ; and when sould we use , Use ; when you want ...

index is not used when exists clause is used
Hi All, Here is the situation I met: CREATE TABLE t1(f1 NUMBER, f2 VARCHAR2(20)); CREATE UNIQUE INDEX i1 ON t1 ( f1 ); CREATE TABLE t2(f1 NUMBER,f2 VARCHAR2(10), t1f1 NUMBER); CREATE INDEX i2 ON t2(f1); after explain following SQL update t1 set f2 = 'abc' where exists(select 1 from t2 where t1.f1 = t2.t1f1 and t2.f1 = 1); The result: 0 UPDATE STATEMENT Optimizer=CHOOSE 1 0 UPDATE OF 'T1' 2 1 FILTER 3 2 TABLE ACCESS (FULL) OF 'T1' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' 5 4 INDEX (RANGE SCAN) OF 'I2...

Just When I Was Getting Used To Using Using
I have a class Packet that contains a nested class Payload and an enumeration: struct Packet { struct Payload { } ; } ; I had gotten into the habit of writing: using Packet::Payload; so I could write: Payload::..blah.. instead of Packet::Payload::..blah I have done this... (gulp) in many, many .cpp files. I tried out the new Visual C++ Express compiler from you-know-who on my source code and got 1000's of error messages: ...\Packet\Packet.cpp(13) : error C2885: 'Packet::Payload': not a valid using-declaration at non-class scope What does the standard say? Is th...

How to use as Functional Index to be used as Primary KEY
Hi, i searched the docs and the archives and was really wondering that i have not found anything searching for "functional index primary key". i would like to have a table of members with nicknames which are case-insensitive but i would like to save the user given name case-sensitive to display 'JimKnopf' instead of 'jimknopf': CREATE TABLE members ( nickname text NOT NULL, CONSTRAINT pk_test PRIMARY KEY (lower(name)) ); psql:scratch.sql:7: ERROR: syntax error at or near "(" at character 92 Of course i can use an index ...

To use locking or not to use it
Hello, I am builiding a multi-threaded application which has the following components : 1 writer thread - it gets information from the network and updates a table of numeric data, then informs the 2 reader threads that there is new information, so they should start running 2 reader threads that are awakened by the writer thread, use the writer thread's table to retrieve updated numerical data to do their calculations. Since the writer updates the table and the readers read from it, I want to use a reader/writer locking mechanism. However, I thought about the following...

Defined but not used? But I am using it!
I can't figure this out. So in a templated class of mine I have the following. namespace menutree { static bool debug = false; } And in various parts of the code I have statements like. if (menutree::debug) { std::cout << "here" << std::endl; Everything compiles but I get the warning "warning: `bool menutree::debug' defined but not used" Can someone shed some light on this? Thanks! Travis wrote: > I can't figure this out. So in a templated class of mine I have the > following. > > namespace menutree > { > static boo...

To use or not to use CLOS
I must admit that I'm completely a newbie in Lisp, as I've been writing C and (some) C++ for years. I don't like the way in wich C++ resolves object programming, although sometimes I'm forced to use object oriented programming in C, in the same way as GTK+ does. Now I emprend a (personal) project and would like to start it from the very beginning in Lisp (my first project in this language). My simple question, and I want to ear answers from all you experts, is: do you recomend me CLOS or perhaps consider that using simple structure and Lisp facilities would be more than enough?...

HowTo find out used (useful) and unused (usesless) indexes?
Hi, Is there a way to find out what indexes are used(useful)/unused(useless) while the last few days/weeks? If possible so tell me a way via data dictionary without using any external tools. Andreas Mosmann -- wenn email, dann AndreasMosmann <bei> web <punkt> de On Apr 17, 4:38=A0am, Andreas Mosmann <mosm...@expires-30-04-2008.news- group.org> wrote: > Hi, > > Is there a way to find out what indexes are used(useful)/unused(useless) > while the last few days/weeks? > If possible so tell me a way via data dictionary without using any >...

Disk in use when not in use
After a hang and hardware reset, I could not boot into eCS 1.14, getting a display that I've never seen before: a VGA display with a white background and a box that said the program pointed to by SET RUNWORKPLACE= could not be loaded. C-A-D worked to shut this display down, but reboot produced the same outcome. This was corrected by rebooting and using an archived desktop. To make sure everything is OK I next booted into my maintenance partition and tried to run CDKDSK C: /F. It wouldn't run because the disk was "in use or locked by another process". Excep...

Use and usefulness of the as syntax
First, could you confirm the following syntax import foo as f equivalent to import foo f = foo Now, I was wondering about the usefulness in everyday programming of the as syntax within an import statement. Here are some instances retrieved from real code of such a syntax import numpy as np import math as _math import pickle as pickle -- In the first case, the syntax is motivated by brevity need, isn't it ? -- The second case seems to be rather widespread and causes math attribute to be private but I don't figure out why this matters. -- In the last case, I can see no poin...

file in use, could not use
Hi. I have two databases that are both linked to the same backend table. I can't use them both at the same time, yet other users can have both functioning simultaneously on their machines. Opening them is not the problem. ..but the error message appears when I run any queries linked to the table. It works in one database, but not the other. All users are running XP sp2 with Access 2003. The error message is the same regardless if anyone is using the backend or not. I believe it is machine specific...MY machine. I haven't a clue. Any ideas? -- Message posted via http://www.accessmon...

Used Cisco Used Switch Used Cisco Router Used Cisco Switch At LinkWaves Corp
Hello Guy. I just bought a cisco 7206VXR from http://www.linkwaves.com. Quick shipping fedex 3 days saver for $85. I think they are great Quick Ship Great Services Excelent Products For Quick Quote. Please visit here http://www.linkwaves.com/requestquote.asp For Quick Search. Please visit here http://www.linkwaves.com/productsearch.asp Their Contact is LinkWaves Corp 29980 Technology Drive, Suite 6 Murrieta, CA 92563 Tel: +1-909-725-9143 Fax: =1-707-221-3762 http://www.linkwaves.com sales(at)linkwaves.com ...

ActGen to use or not to use?
Hello, I was doing a project with Actel FPGA ex128 with Libero Platinum Eval version software. in my project I had to use some counters,Multiplexers etc. which I wrote myself. Now as I was a beginner I didnt use the ActGen. Is it worth the effort to write the code partly by using ActGen macros for counter and muxes and etc. Thanks Naimesh Naimesh wrote: > I was doing a project with Actel FPGA ex128 with Libero Platinum Eval > version software. > > in my project I had to use some counters,Multiplexers etc. which I > wrote myself. Good work. Writing your own code makes y...

If you used to use Windows or now used Windows less because of FreeBSD why?
For a Graduation Project, I am comparing Open-Source software to Propetarity Software. So if you used to use Windows or now use Windows less, why do you use FreeBSD more now? Yes, I know I asked a question like this before, but forgot to ask this one. ;). I continue to use windows on the desktop because of certain apps and games that only run on windows. Making the switch to another platorm is not a priority. I am quite good with windows security and other platforms offer no functionality that windows lacks, in my instance. I recently began utilizing freebsd for some of ...

Replacing values in 2D array using index
Hi I have two arrays (2D) and would like to copy some values from array2 to array1. The exact index location in both arrays are known. Can it be done without using a for loop? Here is the simple version of the code that I am using. Just need to do it without a for loop. Any help will be much appreciated. % A and B are two arrays, pA and pB are index location in array A and B repectively. % The goal is to copy values from pB locations of array B to pA locations in array A. % Without a forloop. A=round(5*abs(randn(2,9))); B=round(9*abs(randn(2,5))); A1=A; pA = [1,2;1,5;1,7;2,3;2,6;2,7]'; pB...

Pattern Matching using LIKE predicate on a DB2 column using INDEX
I have a column in db2 with an index. When I search using LIKE predicate, sometimes it gives results very slow (about 3 to 5 mins). But after the initial hit, subsequent searches using different expressions return very fast (2 secs). It is almost as-if DB2 decides to cache the whole index when we run the query first time. After couple of hours later (or next day) again the initial query takes the hit. Any ideas ? <govindrjujare@gmail.com> wrote in message news:1194399689.880857.184960@o80g2000hse.googlegroups.com... > > I have a column in db2 with an inde...

use case diagram question - actor generalization
This is the description of the problem we are trying to model: We have two actors: 1) record owner and 2) administrator A record owner can add records to the system (i.e., the application). A record owner can also update, view, delete those records he/she has added. In other words, they can only manage the records they own. For instance, let's say Peter and Bill are record owners. Peter can only manage those records he owns and cannot do anything with Bill's records. On the other hand, an administrator has access to all records stored in the system. Administrators can manage (add, vi...

Using a DLL that uses a second library which use Java Runtime Environment
Hello, I am having problems when I try to import a DLL in Matlab that I created myself with the Visual C++ 2010 express. My library uses a second library that I don't want Matlab to worry about (since it uses complex data structures and don't know how it has been created). My library exports the function prototypes with C support (I use -> extern "C" __declspec(dllexport)). I made a simple console application in C++ that uses my library and it works correctly. Matlab loads my library without any problem. The problem comes when I call a function that defines a variable sp...

Used Cisco Used Cisco Router Used Cisco Switch At LinkWaves Corp
Hello Guys, Please let me know if you need Cisco. Will take offers WS-X6704-10GE $8390 (CFC) WS-X6724-SFP $7300 ( CFC) PA-MC-8TE1+ $5295 PA-MC-STM-1SMI $12900 4GE-SFP-LC $21995 WS-F6700-DFC3BXL $7400 SA-VAM, $1199/each WS-C3750G-24PS-S, $3599/each 6E3-SMB, $5500/each 8FE-FX-SC-B, $899/each PA-A3-E3, $799/each PA-MC-8T1 $1500/each OSM-8OC3-POS-MM $3500/each OSM-2OC12-POS-MM $3499/each OSM-2OC12-POS-SI $5599/each WS-X6502-10GE $1995/each PA-A3-8E1-IMA $1550/each PA-A3-8T1-IMA $1440/each PA-MCX-8TE1-M $6200/each PA-MC-8TE1+ $5500/each PA-MC-STM-1SMI $12900/each NM-8T1-IMA $750/each NM-1GE $2200/...

Using indexes
I have sql server backgrounds and I remember that (in a case insensitive environment) a query like this: where car = 'red' also select value like 'RED' or 'Red' In Oracle I'm seeing that it doesn't work this way. It can be simple to do something like this: where lower(car) = 'red' but I remember that this way in sql server indexes are not used, so a table scan is performed. What is the actual behavior of Oracle? Thx to all. On 24 Aug 2006 01:25:54 -0700, devjnr@gmail.com wrote: >I have sql server backgrounds and I remember that (in a case &g...

Web resources about - Why index used/not used - comp.postgresql.general

Steve Jobs Keynote Index Fund
Back in 2007 (and linked from DF at the time), Matt Haughey had this idea: I wondered what effect the Jobs keynote had on Apple’s stock price ...

Money Flow Index
Tuesday, 26 May 2015 Home About us Contact Information Privacy Policy Terms of Service Menu Home Business Economy US Europe Markets Politics ...

City Index UK - Spread Betting, CFD & FX trading
One of the leading UK providers of spread betting and CFD trading, City Index has over 30 years' industry experience.

Index of United States–related articles - Wikipedia, the free encyclopedia
Wikipedia® is a registered trademark of the Wikimedia Foundation, Inc. , a non-profit organization.

Index of /difangxinwen/xxlb_fabu/fbpd_sichuan/201602
Index of /difangxinwen/xxlb_fabu/fbpd_sichuan/201602 Parent Directory t20160201_1984314.html t20160201_1984317.html t20160201_1984322.html t ...

US ranking slips in Heritage Index of Economic Freedom
The Heritage Foundation is out with its annual Index of Economic Freedom and once again, the United States economic rankings fell, marking the ...

Index Ventures Amasses $1.25 Billion to Back Tech Startups
Index Ventures, known for funding startups like Skype and Dropbox, has raised a fresh $550 million fund and expects to accelerate its deal making ...

Index has raised an additional $550 million to invest in US, European, and Israeli tech startups
Index Ventures — the venture capital fund that has backed the likes of Facebook, Dropbox and Deliveroo — has raised $550 million (£376 million) ...

ISM Non-Manufacturing Index Decreased to 53.5% in January
The January ISM Non-manufacturing index was at 53.5%, down from 55.8% in December. The employment index decreased in January to 52.1%, down from ...

Low-Carbon Index Family Launched By Solactive
A low-carbon equity index family has been launched by Solactive AG and the South Pole Group to address regional and global carbon exposures, ...

Resources last updated: 2/15/2016 1:10:23 AM