[SQL] Big table - using wrong index - why?
Hi all,
I have a table with about 6 million records in it.
I have 9 different indexes on the table (different people need to access it
differently)
If you look at the details below, you can see that it's selecting an index
which doesn't have the fields I'm searching with - and it takes for ever.
There is an index which does have the right fields but it's not being used.
I have done a re-index but it didn't help. How can I force it to use index
i_pl_pseq instead of i_pl_loadtimestamp?
Here are the details:
Table "phonelog"
Attribute | Type | Modifier
-+---+--
-
cdate | date | not null
ctime | time |
countrycode | integer |
areacodex | integer |
success | boolean |
carrier | integer |
duration| integer |
phonenumber | character varying(20) |
areacode| character varying(30) |
pseq| bigint|
loadno | bigint|
frline | integer |
entity | character varying(3) | not null
loaddate| date |
loadtime| time |
prefix | character varying(3) |
toline | integer |
dur306 | double precision |
dur180180 | double precision |
recno | bigint| default nextval('SEQ_phonelog'::text)
Indices: i_pl_carrier,
i_pl_date_country_carrier,
i_pl_date_line,
i_pl_entity_date,
i_pl_loadtimestamp,
i_pl_phoneno,
i_pl_prefix,
i_pl_pseq,
i_pl_recno
phones=# \d i_pl_pseq
Index "i_pl_pseq"
Attribute | Type
---+--
entity| character varying(3)
pseq | bigint
btree
phones=# explain select * from phonelog where entity = '001' and pseq >=
9120 and pseq <= 9123;
NOTICE: QUERY PLAN:
Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39
rows=607 width=137)
EXPLAIN
phones=# \d i_pl_loadtimestamp
Index "i_pl_loadtimestamp"
Attribute | Type
---+--
entity| character varying(3)
loaddate | date
loadtime | time
btree
Best regards,
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Why does this plpgslq always return 1?
Stephan Szabo wrote: > On Fri, 27 Jul 2001, John Oakes wrote: > > > Can anyone tell me why this always return 1? Thanks! > > > > CREATE FUNCTION passrate(date, date, text) RETURNS float AS ' > > > > DECLARE > > begindate ALIAS FOR $1; > > enddate ALIAS FOR $2; > > passfail ALIAS FOR $3; > > ret float; > > countp float; > > counttotal float; > > > > > > BEGIN > > > > SELECT INTO countp COUNT(*) > > FROM benchmark > > WHERE passfail = passfail; > > Ehhh? Were you expecting that to substitue > for *one* of those passfails and not the > other? ;) > > Rename the alias variable to something else. > > > > > SELECT INTO counttotal COUNT(*) > > FROM benchmark; > > > > ret := countp / counttotal; And check for zero before doing a division, as a transaction abort is usually not exactly what you want. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Big table - using wrong index - why?
> phones=# \d i_pl_pseq > Index "i_pl_pseq" > Attribute | Type > ---+-- > entity| character varying(3) > pseq | bigint > btree > > phones=# explain select * from phonelog where entity = '001' and pseq >= > 9120 and pseq <= 9123; > NOTICE: QUERY PLAN: > > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > rows=607 width=137) > > EXPLAIN > > phones=# \d i_pl_loadtimestamp > Index "i_pl_loadtimestamp" > Attribute | Type > ---+-- > entity| character varying(3) > loaddate | date > loadtime | time > btree Just a guess, but what happens if you build i_pl_pseq(pseq, entity), i.e. reverse the key fields? Also, has the table been vacuum analyzed? -- Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] plpgsql function return multiple values?
Is it possible for a plpgsql function to return a record? I need to return multiple values, and preferably in the form of a record. Thanks in advance! John ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date Time Functions - ANSI SQL ?
At 01:39 AM 7/30/01 -0400, Tom Lane wrote: >Gonzo Rock <[EMAIL PROTECTED]> writes: >> Are all the date time functions described in the pgSQL docs are >> ANSI-SQL or pgSQL extensions? > >One or the other, yes ;-) Hey Tom, You Clever guy! Yes, One or the other! OK OK! Nothing worse than a programmer who can't be explicit... maybe that's why I struggle so much ;-) QUESTION: I'm trying to find a pgSQL source that documents the Non-Standard pgSQL stuff, the stuff that will break when attempting to execute against mySQL/Oracle/MSSQLServer etc... Immediately: What about the INHERITANCE capabilities... pretty cool but scarry at the same time. (can you tell I'm brand new to SQL and six months late on my current SQL project?) thanks again, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql function return multiple values?
John Oakes wrote: > Is it possible for a plpgsql function to return a record? I need to return > multiple values, and preferably in the form of a record. Thanks in advance! Not useful in any released version of PostgreSQL. In v7.2 you'll have at least the possibility to return a cursor. Don't know yet if I'll be able to add the general RETURN ... AND RESUME. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Big table - using wrong index - why?
Hi Joe, I found the problem - it was a typical "rrrhhh" - error. Since pseq is declared int8, I need to say select * from phonelog where entity = '001' and pseq >= 9120::int8 and pseq <= 9123::int8; (casting the two numbers). Then, it works like a charm ... Best regards, Chris - Original Message - From: "Joe Conway" <[EMAIL PROTECTED]> To: "Chris Ruprecht" <[EMAIL PROTECTED]>; "pgsql" <[EMAIL PROTECTED]> Sent: Monday, July 30, 2001 11:43 AM Subject: Re: [SQL] Big table - using wrong index - why? > > phones=# \d i_pl_pseq > > Index "i_pl_pseq" > > Attribute | Type > > ---+-- > > entity| character varying(3) > > pseq | bigint > > btree > > > > phones=# explain select * from phonelog where entity = '001' and pseq >= > > 9120 and pseq <= 9123; > > NOTICE: QUERY PLAN: > > > > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > > rows=607 width=137) > > > > EXPLAIN > > > > phones=# \d i_pl_loadtimestamp > > Index "i_pl_loadtimestamp" > > Attribute | Type > > ---+-- > > entity| character varying(3) > > loaddate | date > > loadtime | time > > btree > > Just a guess, but what happens if you build i_pl_pseq(pseq, entity), i.e. > reverse the key fields? Also, has the table been vacuum analyzed? > > -- Joe > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Big table - using wrong index - why?
"Chris Ruprecht" <[EMAIL PROTECTED]> writes: > phones=# explain select * from phonelog where entity = '001' and pseq >= > 9120 and pseq <= 9123; > NOTICE: QUERY PLAN: > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > rows=607 width=137) Your problem is that pseq is of type int8 (bigint) but the constants 9120 and 9123 are of type int4 (integer). The system does not currently recognize cross-datatype comparisons as being compatible with indexes. To make the query indexable, you need to cast the constants to be the same type as the indexed column. Here's a simplified example: regression=# create table foo (f1 int8 primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# explain select * from foo where f1 = 42; NOTICE: QUERY PLAN: Seq Scan on foo (cost=0.00..22.50 rows=1 width=8) EXPLAIN regression=# explain select * from foo where f1 = 42::int8; NOTICE: QUERY PLAN: Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1 width=8) EXPLAIN Another way is to write the constants as unknown-type literals (ie, strings) and let the system decide that you meant to let them be int8: regression=# explain select * from foo where f1 = '42'; NOTICE: QUERY PLAN: Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1 width=8) EXPLAIN Variants of this problem arise with int2 and float4 columns, BTW. This is on the to-fix list... see the pghackers list archives for excruciatingly long discussions about how to fix it without breaking other cases... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
