pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBig table - using wrong index - why?
4+ messages / 3 participants
[nested] [flat]
* Big table - using wrong index - why?
@ 2001-07-30 16:20 Chris Ruprecht <chrup999@yahoo.com>
2001-07-30 16:43 ` Re: Big table - using wrong index - why? Joe Conway <joseph.conway@home.com>
2001-07-30 19:20 ` Re: Big table - using wrong index - why? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Chris Ruprecht @ 2001-07-30 16:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big table - using wrong index - why?
2001-07-30 16:20 Big table - using wrong index - why? Chris Ruprecht <chrup999@yahoo.com>
@ 2001-07-30 16:43 ` Joe Conway <joseph.conway@home.com>
2001-07-30 18:54 ` Re: Big table - using wrong index - why? Chris Ruprecht <chrup999@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: Joe Conway @ 2001-07-30 16:43 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big table - using wrong index - why?
2001-07-30 16:20 Big table - using wrong index - why? Chris Ruprecht <chrup999@yahoo.com>
2001-07-30 16:43 ` Re: Big table - using wrong index - why? Joe Conway <joseph.conway@home.com>
@ 2001-07-30 18:54 ` Chris Ruprecht <chrup999@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: Chris Ruprecht @ 2001-07-30 18:54 UTC (permalink / raw)
To: pgsql-sql
Hi Joe,
I found the problem - it was a typical "aaaarrrgggghhh" - 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" <joseph.conway@home.com>
To: "Chris Ruprecht" <chrup999@yahoo.com>; "pgsql"
<pgsql-sql@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Big table - using wrong index - why?
2001-07-30 16:20 Big table - using wrong index - why? Chris Ruprecht <chrup999@yahoo.com>
@ 2001-07-30 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-07-30 19:20 UTC (permalink / raw)
To: Chris Ruprecht <chrup999@yahoo.com>; +Cc: pgsql-sql
"Chris Ruprecht" <chrup999@yahoo.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-07-30 19:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-30 16:20 Big table - using wrong index - why? Chris Ruprecht <chrup999@yahoo.com>
2001-07-30 16:43 ` Joe Conway <joseph.conway@home.com>
2001-07-30 18:54 ` Chris Ruprecht <chrup999@yahoo.com>
2001-07-30 19:20 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox