pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Yet another indexing issue.
6+ messages / 3 participants
[nested] [flat]

* Yet another indexing issue.
@ 2002-03-19 18:40  David Siebert <david@eclipsecat.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 20:50  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: 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 22:06  David Siebert <david@eclipsecat.com>
  parent: 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-20 00:01  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: David Siebert <david@eclipsecat.com>
  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-22 14:37  Pirtea Calin <pcalin@rdsor.ro>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.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-22 18:49  David Siebert <david@eclipsecat.com>
  parent: Pirtea Calin <pcalin@rdsor.ro>
  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