pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing Query
12+ messages / 5 participants
[nested] [flat]
* Optimizing Query
@ 2001-03-05 20:56 Justin Long <justinlong@strategicnetwork.org>
0 siblings, 1 reply; 12+ messages in thread
From: Justin Long @ 2001-03-05 20:56 UTC (permalink / raw)
To: pgsql-sql
Any suggestions welcome!
Here is my query:
select k.*, c.category from knowledge k, kb_categories c , kbwords w0 ,
kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and w0.wordid=42743)
AND (k.kbid=w1.kbid and w1.wordid=85369)) ORDER BY k.kbid DESC LIMIT 25;
Now for the details
knowledge k = 3,150 records
kbwords = 2-field database (kbid, wordid), 825,748 records
Each word in the knowledge base is stored in a database called wordindex,
which has 50,000 records or so. The system first explodes the query string
and pulls the word #s from this database, which is where we get 42743 and
85369 above, "ASIA" and "CHILDREN" respectively.) The idea is then to pull
all the articles in the knowledge base which contain both of these words.
Here is the EXPLAIN for the query:
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..527690060.67 rows=2878549 width=308)
-> Nested Loop (cost=0.00..9472443.40 rows=52582 width=304)
-> Nested Loop (cost=0.00..6278.63 rows=960 width=300)
-> Index Scan Backward using knowledge_kbid_key on
knowledge k (cost=0.00..1292.51 rows=2825 width=284)
-> Seq Scan on kb_categories c (cost=0.00..1.34 rows=34
width=16)
-> Seq Scan on kbwords w0 (cost=0.00..9787.02 rows=5474 width=4)
-> Seq Scan on kbwords w1 (cost=0.00..9787.02 rows=5474 width=4)
This takes quite a while to return results... prohibitively long. There are
indexes on k.catid, c.catid, k.kbid, w0.kbid, w0.wordid. Any suggestions
for further optimization would be very welcome. We get about 3,000 searches
on our database daily...
Blessings,
Justin Long
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:07 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Justin Long <justinlong@strategicnetwork.org>
0 siblings, 1 reply; 12+ messages in thread
From: Bruce Momjian @ 2001-03-05 21:07 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; +Cc: pgsql-sql
Have you tried VACUUM ANALYZE and CLUSTER?
> Any suggestions welcome!
>
> Here is my query:
>
> select k.*, c.category from knowledge k, kb_categories c , kbwords w0 ,
> kbwords w1 WHERE k.catid=c.catid AND ((k.kbid=w0.kbid and w0.wordid=42743)
> AND (k.kbid=w1.kbid and w1.wordid=85369)) ORDER BY k.kbid DESC LIMIT 25;
>
> Now for the details
> knowledge k = 3,150 records
> kbwords = 2-field database (kbid, wordid), 825,748 records
>
> Each word in the knowledge base is stored in a database called wordindex,
> which has 50,000 records or so. The system first explodes the query string
> and pulls the word #s from this database, which is where we get 42743 and
> 85369 above, "ASIA" and "CHILDREN" respectively.) The idea is then to pull
> all the articles in the knowledge base which contain both of these words.
>
> Here is the EXPLAIN for the query:
>
> NOTICE: QUERY PLAN:
>
> Nested Loop (cost=0.00..527690060.67 rows=2878549 width=308)
> -> Nested Loop (cost=0.00..9472443.40 rows=52582 width=304)
> -> Nested Loop (cost=0.00..6278.63 rows=960 width=300)
> -> Index Scan Backward using knowledge_kbid_key on
> knowledge k (cost=0.00..1292.51 rows=2825 width=284)
> -> Seq Scan on kb_categories c (cost=0.00..1.34 rows=34
> width=16)
> -> Seq Scan on kbwords w0 (cost=0.00..9787.02 rows=5474 width=4)
> -> Seq Scan on kbwords w1 (cost=0.00..9787.02 rows=5474 width=4)
>
> This takes quite a while to return results... prohibitively long. There are
> indexes on k.catid, c.catid, k.kbid, w0.kbid, w0.wordid. Any suggestions
> for further optimization would be very welcome. We get about 3,000 searches
> on our database daily...
>
> Blessings,
> Justin Long
>
>
>
> ____________________________________________________________________
> Justin Long Network for Strategic Missions
> justinlong@strategicnetwork.org 1732 South Park Court
> http://www.strategicnetwork.org Chesapeake, VA 23320, USA
> Reality Check e-zine: reality-check-subscribe@yahoogroups.com
> ____________________________________________________________________
> Law: Never retreat. Never surrender. Never cut a deal with a dragon.
> Corollary: No armor? Unclean life? Then do not mess in the affairs
> of dragons, for you are crunchy and taste good with ketchup.
>
>
> ____________________________________________________________________
> Justin Long Network for Strategic Missions
> justinlong@strategicnetwork.org 1732 South Park Court
> http://www.strategicnetwork.org Chesapeake, VA 23320, USA
> Reality Check e-zine: reality-check-subscribe@yahoogroups.com
> ____________________________________________________________________
> Law: Never retreat. Never surrender. Never cut a deal with a dragon.
> Corollary: No armor? Unclean life? Then do not mess in the affairs
> of dragons, for you are crunchy and taste good with ketchup.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:40 Mathijs Brands <mathijs@ilse.nl>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 4 replies; 12+ messages in thread
From: Mathijs Brands @ 2001-03-05 21:40 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Justin Long <justinlong@strategicnetwork.org>; pgsql-sql
On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> Have you tried VACUUM ANALYZE and CLUSTER?
I assume CLUSTER still drops all indexes except the one you're clustering
on?
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:42 Justin Long <justinlong@strategicnetwork.org>
parent: Mathijs Brands <mathijs@ilse.nl>
3 siblings, 0 replies; 12+ messages in thread
From: Justin Long @ 2001-03-05 21:42 UTC (permalink / raw)
To: pgsql-sql
Does that mean that if you have 3 indexes on a table and you cluster one,
it deletes the other 2?
At 04:45 PM 3/5/2001 -0500, you wrote:
>Yes.
>
> > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > > Have you tried VACUUM ANALYZE and CLUSTER?
> >
> > I assume CLUSTER still drops all indexes except the one you're clustering
> > on?
> >
> > Mathijs
> > --
> > It's not that perl programmers are idiots, it's that the language
> > rewards idiotic behavior in a way that no other language or tool has
> > ever done.
> > Erik Naggum
> >
>
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:44 Justin Long <justinlong@strategicnetwork.org>
parent: Mathijs Brands <mathijs@ilse.nl>
3 siblings, 0 replies; 12+ messages in thread
From: Justin Long @ 2001-03-05 21:44 UTC (permalink / raw)
To: pgsql-sql
Yes, it drops indexes, much to my chagrin, as I just realized ... including
SERIALs...
Justin
At 04:45 PM 3/5/2001 -0500, you wrote:
>Yes.
>
> > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > > Have you tried VACUUM ANALYZE and CLUSTER?
> >
> > I assume CLUSTER still drops all indexes except the one you're clustering
> > on?
> >
> > Mathijs
> > --
> > It's not that perl programmers are idiots, it's that the language
> > rewards idiotic behavior in a way that no other language or tool has
> > ever done.
> > Erik Naggum
> >
>
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:45 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Mathijs Brands <mathijs@ilse.nl>
3 siblings, 0 replies; 12+ messages in thread
From: Bruce Momjian @ 2001-03-05 21:45 UTC (permalink / raw)
To: Mathijs Brands <mathijs@ilse.nl>; +Cc: Justin Long <justinlong@strategicnetwork.org>; pgsql-sql
Yes.
> On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > Have you tried VACUUM ANALYZE and CLUSTER?
>
> I assume CLUSTER still drops all indexes except the one you're clustering
> on?
>
> Mathijs
> --
> It's not that perl programmers are idiots, it's that the language
> rewards idiotic behavior in a way that no other language or tool has
> ever done.
> Erik Naggum
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 21:59 Justin Long <justinlong@strategicnetwork.org>
parent: Mathijs Brands <mathijs@ilse.nl>
3 siblings, 4 replies; 12+ messages in thread
From: Justin Long @ 2001-03-05 21:59 UTC (permalink / raw)
To: pgsql-sql
Ok, now I have another question... it doesn't seem to be accessing the index.
explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
w1.wordid=85369))
NOTICE: QUERY PLAN:
Merge Join (cost=32339.30..35496.97 rows=19262538 width=24)
-> Merge Join (cost=16530.24..16668.77 rows=233274 width=20)
-> Sort (cost=15809.06..15809.06 rows=8257 width=4)
-> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257
width=4)
-> Sort (cost=721.18..721.18 rows=2825 width=16)
-> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825
width=16)
-> Sort (cost=15809.06..15809.06 rows=8257 width=4)
-> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4)
Note the sequential scans... there is a wordindex where w0.wordid=42743...
why isn't it doing an indexscan? wouldn't that be more efficient?
Justin
At 04:45 PM 3/5/2001 -0500, you wrote:
>Yes.
>
> > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > > Have you tried VACUUM ANALYZE and CLUSTER?
> >
> > I assume CLUSTER still drops all indexes except the one you're clustering
> > on?
> >
> > Mathijs
> > --
> > It's not that perl programmers are idiots, it's that the language
> > rewards idiotic behavior in a way that no other language or tool has
> > ever done.
> > Erik Naggum
> >
>
>
>--
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 22:53 Michael Fork <mfork@toledolink.com>
parent: Justin Long <justinlong@strategicnetwork.org>
3 siblings, 0 replies; 12+ messages in thread
From: Michael Fork @ 2001-03-05 22:53 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; +Cc: pgsql-sql
Did you run VACUUM ANALYZE after running CLUSTER?
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Mon, 5 Mar 2001, Justin Long wrote:
> Ok, now I have another question... it doesn't seem to be accessing the index.
>
> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> w1.wordid=85369))
>
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=32339.30..35496.97 rows=19262538 width=24)
> -> Merge Join (cost=16530.24..16668.77 rows=233274 width=20)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257
> width=4)
> -> Sort (cost=721.18..721.18 rows=2825 width=16)
> -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825
> width=16)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4)
>
> Note the sequential scans... there is a wordindex where w0.wordid=42743...
> why isn't it doing an indexscan? wouldn't that be more efficient?
>
> Justin
>
>
> At 04:45 PM 3/5/2001 -0500, you wrote:
> >Yes.
> >
> > > On Mon, Mar 05, 2001 at 04:07:57PM -0500, Bruce Momjian allegedly wrote:
> > > > Have you tried VACUUM ANALYZE and CLUSTER?
> > >
> > > I assume CLUSTER still drops all indexes except the one you're clustering
> > > on?
> > >
> > > Mathijs
> > > --
> > > It's not that perl programmers are idiots, it's that the language
> > > rewards idiotic behavior in a way that no other language or tool has
> > > ever done.
> > > Erik Naggum
> > >
> >
> >
> >--
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman@candle.pha.pa.us | (610) 853-3000
> > + If your life is a hard drive, | 830 Blythe Avenue
> > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
>
>
> ____________________________________________________________________
> Justin Long Network for Strategic Missions
> justinlong@strategicnetwork.org 1732 South Park Court
> http://www.strategicnetwork.org Chesapeake, VA 23320, USA
> Reality Check e-zine: reality-check-subscribe@yahoogroups.com
> ____________________________________________________________________
> Law: Never retreat. Never surrender. Never cut a deal with a dragon.
> Corollary: No armor? Unclean life? Then do not mess in the affairs
> of dragons, for you are crunchy and taste good with ketchup.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-05 23:08 Mathijs Brands <mathijs@ilse.nl>
parent: Justin Long <justinlong@strategicnetwork.org>
3 siblings, 0 replies; 12+ messages in thread
From: Mathijs Brands @ 2001-03-05 23:08 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; +Cc: pgsql-sql
On Mon, Mar 05, 2001 at 04:59:47PM -0500, Justin Long allegedly wrote:
> Ok, now I have another question... it doesn't seem to be accessing the index.
>
> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> w1.wordid=85369))
>
> NOTICE: QUERY PLAN:
>
> Merge Join (cost=32339.30..35496.97 rows=19262538 width=24)
> -> Merge Join (cost=16530.24..16668.77 rows=233274 width=20)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257
> width=4)
> -> Sort (cost=721.18..721.18 rows=2825 width=16)
> -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825
> width=16)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4)
>
> Note the sequential scans... there is a wordindex where w0.wordid=42743...
> why isn't it doing an indexscan? wouldn't that be more efficient?
>
> Justin
Did you run the 'vacuum analyze' command on the tables concerned (or even
better, the whole database)? Without the data this analysis provides psql
cannot come up with a good execution plan and falls back to full table
scans. Do a 'vacuum analyze' one a week to keep performance levels up.
Cheers,
Mathijs
--
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
Erik Naggum
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-06 04:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Long <justinlong@strategicnetwork.org>
3 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-03-06 04:10 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; +Cc: pgsql-sql
Justin Long <justinlong@strategicnetwork.org> writes:
> Ok, now I have another question... it doesn't seem to be accessing the index.
> explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> w1.wordid=85369))
> NOTICE: QUERY PLAN:
> Merge Join (cost=32339.30..35496.97 rows=19262538 width=24)
> -> Merge Join (cost=16530.24..16668.77 rows=233274 width=20)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257
> width=4)
> -> Sort (cost=721.18..721.18 rows=2825 width=16)
> -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825
> width=16)
> -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257 width=4)
> Note the sequential scans... there is a wordindex where w0.wordid=42743...
> why isn't it doing an indexscan? wouldn't that be more efficient?
It probably thinks not, because the estimated number of hits (8257) is
so high. That estimate is currently driven by the frequency of the most
common value in the column (mainly because that's the only stat we have
:-(). I am guessing that you have a few very common words, which are
skewing the stats for kbwords and causing it not to pick an indexscan.
Does your setup have a notion of "stop words" that shouldn't be indexed,
like "a", "an", "the", etc? Perhaps you need to add such a feature, or
throw in a few more stopwords if you already have 'em.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-06 14:18 Justin Long <justinlong@strategicnetwork.org>
parent: Justin Long <justinlong@strategicnetwork.org>
3 siblings, 1 reply; 12+ messages in thread
From: Justin Long @ 2001-03-06 14:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Wow. I can't believe the difference. It didn't take too long. I'll set up a
script in my etc/cron.weekly to run it... would there be any benefit to
doing a vacuum analyze nightly?
Justin Long
At 11:10 PM 3/5/2001 -0500, you wrote:
>Justin Long <justinlong@strategicnetwork.org> writes:
> > Ok, now I have another question... it doesn't seem to be accessing the
> index.
>
> > explain select k.kbid,k.title from knowledge k , kbwords w0 , kbwords w1
> > WHERE ((k.kbid=w0.kbid and w0.wordid=42743) AND (k.kbid=w1.kbid and
> > w1.wordid=85369))
>
> > NOTICE: QUERY PLAN:
>
> > Merge Join (cost=32339.30..35496.97 rows=19262538 width=24)
> > -> Merge Join (cost=16530.24..16668.77 rows=233274 width=20)
> > -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> > -> Seq Scan on kbwords w1 (cost=0.00..15271.85 rows=8257
> > width=4)
> > -> Sort (cost=721.18..721.18 rows=2825 width=16)
> > -> Seq Scan on knowledge k (cost=0.00..559.25 rows=2825
> > width=16)
> > -> Sort (cost=15809.06..15809.06 rows=8257 width=4)
> > -> Seq Scan on kbwords w0 (cost=0.00..15271.85 rows=8257
> width=4)
>
> > Note the sequential scans... there is a wordindex where w0.wordid=42743...
> > why isn't it doing an indexscan? wouldn't that be more efficient?
>
>It probably thinks not, because the estimated number of hits (8257) is
>so high. That estimate is currently driven by the frequency of the most
>common value in the column (mainly because that's the only stat we have
>:-(). I am guessing that you have a few very common words, which are
>skewing the stats for kbwords and causing it not to pick an indexscan.
>
>Does your setup have a notion of "stop words" that shouldn't be indexed,
>like "a", "an", "the", etc? Perhaps you need to add such a feature, or
>throw in a few more stopwords if you already have 'em.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
____________________________________________________________________
Justin Long Network for Strategic Missions
justinlong@strategicnetwork.org 1732 South Park Court
http://www.strategicnetwork.org Chesapeake, VA 23320, USA
Reality Check e-zine: reality-check-subscribe@yahoogroups.com
____________________________________________________________________
Law: Never retreat. Never surrender. Never cut a deal with a dragon.
Corollary: No armor? Unclean life? Then do not mess in the affairs
of dragons, for you are crunchy and taste good with ketchup.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Optimizing Query
@ 2001-03-06 15:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Long <justinlong@strategicnetwork.org>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2001-03-06 15:31 UTC (permalink / raw)
To: Justin Long <justinlong@strategicnetwork.org>; +Cc: pgsql-sql
Justin Long <justinlong@strategicnetwork.org> writes:
> Wow. I can't believe the difference. It didn't take too long. I'll set up a
> script in my etc/cron.weekly to run it... would there be any benefit to
> doing a vacuum analyze nightly?
Depends. A nightly vacuum is probably good practice, but you could skip
the analyze part if your data statistics (such as column min and max
values) don't change much.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2001-03-06 15:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-05 20:56 Optimizing Query Justin Long <justinlong@strategicnetwork.org>
2001-03-05 21:07 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:40 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-05 21:42 ` Justin Long <justinlong@strategicnetwork.org>
2001-03-05 21:44 ` Justin Long <justinlong@strategicnetwork.org>
2001-03-05 21:45 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-05 21:59 ` Justin Long <justinlong@strategicnetwork.org>
2001-03-05 22:53 ` Michael Fork <mfork@toledolink.com>
2001-03-05 23:08 ` Mathijs Brands <mathijs@ilse.nl>
2001-03-06 04:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-06 14:18 ` Justin Long <justinlong@strategicnetwork.org>
2001-03-06 15:31 ` 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