pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedYet another indexing issue.
6+ messages / 3 participants
[nested] [flat]
* Yet another indexing issue.
@ 2002-03-19 18:40 David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Siebert @ 2002-03-19 18:40 UTC (permalink / raw)
To: pgsql-general@postgresql.org
I am having issues with an Index.
Here is the query
SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
I have tried '0' and jut plain 0 as well as type casting it with ::int4 .
Here is the index I think it should use.
CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
istatus int4_ops);
But it is still doing a scan?
Any suggestions?
yes I have used Vacumme with the anylise option.
Thanks for any info.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Yet another indexing issue.
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
@ 2002-03-19 20:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-19 22:06 ` Re: Yet another indexing issue. David Siebert <david@eclipsecat.com>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2002-03-19 20:50 UTC (permalink / raw)
To: David Siebert <david@eclipsecat.com>; +Cc: pgsql-general@postgresql.org
On Tue, 19 Mar 2002, David Siebert wrote:
>
> I am having issues with an Index.
> Here is the query
>
> SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
> sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
> I have tried '0' and jut plain 0 as well as type casting it with ::int4 .
>
> Here is the index I think it should use.
>
> CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
> istatus int4_ops);
> But it is still doing a scan?
>
> Any suggestions?
> yes I have used Vacumme with the anylise option.
What is the schema (probably not meaningful but always helps), what does
explain show for the query (specifically for the row counds), does using
set enable_seqscan=off change the explain output?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Yet another indexing issue.
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-03-19 22:06 ` David Siebert <david@eclipsecat.com>
2002-03-20 00:01 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Siebert @ 2002-03-19 22:06 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-general@postgresql.org
Here is the explain output
Sort (cost=293.24..293.24 rows=1 width=128)
-> Seq Scan on phonecalls (cost=0.00..293.23 rows=1 width=128)
Here is the Table
CREATE TABLE "phonecalls" (
"irecnum" int4 DEFAULT nextval('"phonecalls_irecnum_seq"'::text) NOT NULL,
"scaller" varchar(80),
"sphone" varchar(40),
"sphone2" varchar(40),
"squedby" varchar(40),
"sfor" varchar(40),
"dplaceddate" timestamp,
"dtakendate" timestamp,
"dresdate" timestamp,
"ipri" int4,
"istatus" int4,
"iresolution" int4,
"ireques" int4,
"snotes" varchar(3999),
"stakenby" varchar(40),
CONSTRAINT "phonecalls_irecnum_key" UNIQUE ("irecnum")
);
Where would one find set enable_seqscan=off; in the docs?
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Tuesday, March 19, 2002 3:51 PM
To: David Siebert
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Yet another indexing issue.
On Tue, 19 Mar 2002, David Siebert wrote:
>
> I am having issues with an Index.
> Here is the query
>
> SELECT * FROM phonecalls WHERE istatus = 0 AND (sfor = 'pat' OR
> sfor='TECHIES') ORDER BY ipri DESC, dplaceddate;
> I have tried '0' and jut plain 0 as well as type casting it with ::int4 .
>
> Here is the index I think it should use.
>
> CREATE INDEX phonecallspoll ON phonecalls USING btree (sfor varchar_ops,
> istatus int4_ops);
> But it is still doing a scan?
>
> Any suggestions?
> yes I have used Vacumme with the anylise option.
What is the schema (probably not meaningful but always helps), what does
explain show for the query (specifically for the row counds), does using
set enable_seqscan=off change the explain output?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Yet another indexing issue.
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-19 22:06 ` Re: Yet another indexing issue. David Siebert <david@eclipsecat.com>
@ 2002-03-20 00:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-22 14:37 ` Re: Yet another indexing issue. Pirtea Calin <pcalin@rdsor.ro>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2002-03-20 00:01 UTC (permalink / raw)
To: David Siebert <david@eclipsecat.com>; +Cc: pgsql-general@postgresql.org
On Tue, 19 Mar 2002, David Siebert wrote:
> Here is the explain output
>
> Sort (cost=293.24..293.24 rows=1 width=128)
> -> Seq Scan on phonecalls (cost=0.00..293.23 rows=1 width=128)
>
> Here is the Table
> CREATE TABLE "phonecalls" (
> "irecnum" int4 DEFAULT nextval('"phonecalls_irecnum_seq"'::text) NOT NULL,
> "scaller" varchar(80),
> "sphone" varchar(40),
> "sphone2" varchar(40),
> "squedby" varchar(40),
> "sfor" varchar(40),
> "dplaceddate" timestamp,
> "dtakendate" timestamp,
> "dresdate" timestamp,
> "ipri" int4,
> "istatus" int4,
> "iresolution" int4,
> "ireques" int4,
> "snotes" varchar(3999),
> "stakenby" varchar(40),
> CONSTRAINT "phonecalls_irecnum_key" UNIQUE ("irecnum")
> );
>
> Where would one find set enable_seqscan=off; in the docs?
To be honest I'm not sure where it's mentioned. It's a big tool that
lets you make some gross changes to the optimizer's planning (sets the
cost of sequence scan very very high).
Does doing it before the explain change the output?
Also, does making an index on phonecalls(istatus, sfor) [rather than
sfor,istatus] change the choice?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Yet another indexing issue.
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-19 22:06 ` Re: Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-20 00:01 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-03-22 14:37 ` Pirtea Calin <pcalin@rdsor.ro>
2002-03-22 18:49 ` Re: Yet another indexing issue. David Siebert <david@eclipsecat.com>
0 siblings, 1 reply; 6+ messages in thread
From: Pirtea Calin @ 2002-03-22 14:37 UTC (permalink / raw)
To: pgsql-general@postgresql.org
> > Where would one find set enable_seqscan=off; in the docs?
>
> To be honest I'm not sure where it's mentioned. It's a big tool that
> lets you make some gross changes to the optimizer's planning (sets the
> cost of sequence scan very very high).
>
I found this in Admin.pdf
3.4.1. Planner and Optimizer Tuning
page 37/38
ENABLE_SEQSCAN (boolean)
Enables or disables the query planner's use of sequential scan plan
types. It's not possible to
suppress sequential scans entirely, but turning this variable off
discourages the planner from using
one if there is any other method available. The default is on. This is
mostly useful to debug the
query planner.
--
Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@rdsor.ro
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Yet another indexing issue.
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-19 22:06 ` Re: Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-20 00:01 ` Re: Yet another indexing issue. Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-22 14:37 ` Re: Yet another indexing issue. Pirtea Calin <pcalin@rdsor.ro>
@ 2002-03-22 18:49 ` David Siebert <david@eclipsecat.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Siebert @ 2002-03-22 18:49 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Thank you. I found the error. It was the order of the fields in the index.
I do not rember seeing that the order made a difference in the indexing. It
is logical now that I think of it.
I for the life of me could not find where in PgAdminII I could set the order
of the fields in when creating an index so I did it by hand.
Thanks again.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Pirtea Calin
Sent: Friday, March 22, 2002 9:38 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Yet another indexing issue.
> > Where would one find set enable_seqscan=off; in the docs?
>
> To be honest I'm not sure where it's mentioned. It's a big tool that
> lets you make some gross changes to the optimizer's planning (sets the
> cost of sequence scan very very high).
>
I found this in Admin.pdf
3.4.1. Planner and Optimizer Tuning
page 37/38
ENABLE_SEQSCAN (boolean)
Enables or disables the query planner's use of sequential scan plan
types. It's not possible to
suppress sequential scans entirely, but turning this variable off
discourages the planner from using
one if there is any other method available. The default is on. This is
mostly useful to debug the
query planner.
--
Best regards,
Aplication Developer
Pirtea Calin Iancu
S.C. SoftScape S.R.L.
pcalin@rdsor.ro
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-03-22 18:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-19 18:40 Yet another indexing issue. David Siebert <david@eclipsecat.com>
2002-03-19 20:50 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-19 22:06 ` David Siebert <david@eclipsecat.com>
2002-03-20 00:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-22 14:37 ` Pirtea Calin <pcalin@rdsor.ro>
2002-03-22 18:49 ` David Siebert <david@eclipsecat.com>
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