|
Home > Archive > PostgreSQL Performance > January 2006 > Index isn't used during a join.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Index isn't used during a join.
|
|
| Robert Creager 2006-01-10, 3:24 am |
| | |
| Michael Fuhr 2006-01-10, 3:24 am |
| On Mon, Jan 09, 2006 at 09:23:38PM -0700, Robert Creager wrote:
> I'm working with a query to get more info out with a join. The base
> query works great speed wise because of index usage. When the join is
> tossed in, the index is no longer used, so the query performance tanks.
The first query you posted returns 285 rows and the second returns
over one million; index usage aside, that difference surely accounts
for a performance penalty. And as is often pointed out, index scans
aren't always faster than sequential scans: the more of a table a
query has to fetch, the more likely a sequential scan will be faster.
Have the tables been vacuumed and analyzed? The planner's estimates
for windspeed are pretty far off, which could be affecting the query
plan:
> -> Sort (cost=12997.68..13157.98 rows=64120 width=28) (actual time=2286.155..2286.450 rows=284 loops=1)
> Sort Key: date_part('doy'::tex
t, unmunge_time(windspe
ed.time_group))
> -> Seq Scan on windspeed (cost=0.00..7878.18 rows=64120 width=28) (actual time=2279.275..2285.271 rows=284 loops=1)
> Filter: (unmunge_time(time_g
roup) > (now() - '24:00:00'::interval
))
That's a small amount of the total query time, however, so although
an index scan might help it probably won't provide the big gain
you're looking for.
Have you done any tests with enable_seqscan disabled? That'll show
whether an index or bitmap scan would be faster. And have you
verified that the join condition is correct? Should the query be
returning over a million rows?
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
| |
| Robert Creager 2006-01-10, 11:24 am |
| | |
| Robert Creager 2006-01-11, 3:24 am |
| | |
| Michael Fuhr 2006-01-11, 3:24 am |
| On Tue, Jan 10, 2006 at 10:10:55PM -0700, Robert Creager wrote:
> The query is now correct, but still is slow because of lack of
> index usage. I don't know how to structure the query correctly to
> use the index.
Have you tried adding restrictions on doy in the WHERE clause?
Something like this, I think:
WHERE ...
AND doy >= EXTRACT(doy FROM now() - '24 hour'::interval)
AND doy <= EXTRACT(doy FROM now())
Something else occurred to me: do you (or will you) have more than
one year of data? If so then matching on doy could be problematic
unless you also check for the year, or unless you want to match
more than one year.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Michael Fuhr 2006-01-11, 3:24 am |
| On Wed, Jan 11, 2006 at 12:56:55AM -0700, Michael Fuhr wrote:
> WHERE ...
> AND doy >= EXTRACT(doy FROM now() - '24 hour'::interval)
> AND doy <= EXTRACT(doy FROM now())
To work on 1 Jan this should be more like
WHERE ...
AND (doy = EXTRACT(doy FROM now() - '24 hour'::interval) OR
doy = EXTRACT(doy FROM now()))
In any case the point is to add conditions to the WHERE clause that
will use an index on the table for which you're currently getting
a sequential scan.
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Robert Creager 2006-01-11, 9:24 am |
| | |
| Robert Creager 2006-01-11, 9:24 am |
| | |
| Tom Lane 2006-01-11, 11:24 am |
| Robert Creager < Robert_Creager@Logic
alChaos.org> writes:
> What I had thought is that PG would (could?) be smart enough to realize tha=
> t one query was restricted, and apply that restriction to the other based o=
> n the join. I know it works in other cases (using indexes on both tables u=
> sing the join)...
The planner understands about transitivity of equality, ie given a = b
and b = c it can infer a = c. It doesn't do any such thing for
inequalities though, nor does it deduce f(a) = f(b) for arbitrary
functions f. The addition Michael suggested requires much more
understanding of the properties of the functions in your query than
I think would be reasonable to put into the planner.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Michael Fuhr 2006-01-11, 11:24 am |
| On Wed, Jan 11, 2006 at 08:02:37AM -0700, Robert Creager wrote:
> The query is wrong as stated, as it won't work when the interval
> crosses a year boundary, but it's a stop gap for now.
Yeah, I realized that shortly after I posted the original and posted
a correction.
http://archives.postgresql.org/pgsq...01/msg00104.php
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Robert Creager 2006-01-12, 3:25 am |
| |
|
|
|
|