pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery Tuning
11+ messages / 6 participants
[nested] [flat]
* Query Tuning
@ 2004-10-08 14:49 Pallav Kalva <pkalva@deg.cc>
2004-10-08 16:42 ` Re: Query Tuning Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Pallav Kalva @ 2004-10-08 14:49 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a problem with the below query, when i do explain on the
below query on my live database it doesnt use any index specified on the
tables and does seq scan on the table which is 400k records. But if i
copy the same table onto a different database on a different machine it
uses all the indexes specified and query runs much quicker. I ran
analyze, vacuum analyze and rebuilt indexes on the live database but
still there is no difference in the performance. Can anyone tell why
this odd behavior ?
Thanks!
Query
--------
SELECT a.total as fsbos, b.total as foreclosures, c.total as
auctions, d.latestDate as lastUpdated
FROM ((SELECT count(1) as total
FROM Properties p INNER JOIN Datasources ds
ON p.datasource = ds.sourceId
WHERE p.countyState = 'GA'
AND ds.sourceType = 'fsbo'
AND p.status in (1,2)
)) a,
((SELECT count(1) as total
FROM Properties p INNER JOIN Datasources ds
ON p.datasource = ds.sourceId
WHERE p.countyState = 'GA'
AND ds.sourceType = 'foreclosure'
AND (p.status in (1,2)
OR (p.status = 0 AND p.LastReviewed2 >= current_timestamp -
INTERVAL '14 days') )
)) b,
((SELECT count(1) as total
FROM Properties p
WHERE p.datasource = 1087
AND p.countyState = 'GA'
AND p.status in (1,2)
)) c,
((SELECT to_char(max(entryDate2), 'MM/DD/YYYY HH24:MI') as latestDate
FROM Properties p
WHERE p.countyState = 'GA'
)) d
Explain from the Live database
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1334730.95..1334731.02 rows=1 width=56)
-> Nested Loop (cost=1026932.25..1026932.30 rows=1 width=24)
-> Nested Loop (cost=704352.11..704352.14 rows=1 width=16)
-> Subquery Scan b (cost=375019.89..375019.90 rows=1
width=8)
-> Aggregate (cost=375019.89..375019.89 rows=1
width=0)
-> Hash Join (cost=308.72..374844.49
rows=70158 width=0)
Hash Cond: ("outer".datasource =
"inner".sourceid)
-> Seq Scan on properties p
(cost=0.00..373289.10 rows=72678 width=4)
Filter: ((countystate =
'GA'::bpchar) AND ((status = 0) OR (status = 1) OR (status = 2)) AND
((lastreviewed2 >= (('now'::text)::timestamp(6) with time zone - '14
days'::interval)) OR (status = 1) OR (status = 2)))
-> Hash (cost=288.05..288.05
rows=8267 width=4)
-> Seq Scan on datasources ds
(cost=0.00..288.05 rows=8267 width=4)
Filter: ((sourcetype)::text
= 'foreclosure'::text)
-> Subquery Scan c (cost=329332.22..329332.23 rows=1
width=8)
-> Aggregate (cost=329332.22..329332.22 rows=1
width=0)
-> Seq Scan on properties p
(cost=0.00..329321.06 rows=4464 width=0)
Filter: ((datasource = 1087) AND
(countystate = 'GA'::bpchar) AND ((status = 1) OR (status = 2)))
-> Subquery Scan a (cost=322580.14..322580.15 rows=1 width=8)
-> Aggregate (cost=322580.14..322580.14 rows=1 width=0)
-> Hash Join (cost=288.24..322579.28 rows=344
width=0)
Hash Cond: ("outer".datasource =
"inner".sourceid)
-> Seq Scan on properties p
(cost=0.00..321993.05 rows=39273 width=4)
Filter: ((countystate = 'GA'::bpchar)
AND ((status = 1) OR (status = 2)))
-> Hash (cost=288.05..288.05 rows=75 width=4)
-> Seq Scan on datasources ds
(cost=0.00..288.05 rows=75 width=4)
Filter: ((sourcetype)::text =
'fsbo'::text)
-> Subquery Scan d (cost=307798.70..307798.72 rows=1 width=32)
-> Aggregate (cost=307798.70..307798.71 rows=1 width=8)
-> Seq Scan on properties p (cost=0.00..307337.04
rows=184666 width=8)
Filter: (countystate = 'GA'::bpchar)
Explain on the Copy of the Live database for the same query
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5380.81..5380.88 rows=1 width=56)
-> Nested Loop (cost=3714.30..3714.35 rows=1 width=48)
-> Nested Loop (cost=2687.15..2687.18 rows=1 width=40)
-> Subquery Scan a (cost=1022.76..1022.77 rows=1 width=8)
-> Aggregate (cost=1022.76..1022.76 rows=1 width=0)
-> Nested Loop (cost=0.00..1022.75 rows=2
width=0)
-> Seq Scan on datasources ds
(cost=0.00..4.44 rows=2 width=4)
Filter: ((sourcetype)::text =
'fsbo'::text)
-> Index Scan using
idx_properties_datasourcestateauctiondate on properties p
(cost=0.00..509.14 rows=2 width=4)
Index Cond: (p.datasource =
"outer".sourceid)
Filter: ((countystate =
'GA'::bpchar) AND ((status = 1) OR (status = 2)))
-> Subquery Scan d (cost=1664.39..1664.40 rows=1 width=32)
-> Aggregate (cost=1664.39..1664.39 rows=1 width=8)
-> Index Scan using properties_idx_search on
properties p (cost=0.00..1663.35 rows=416 width=8)
Index Cond: (countystate = 'GA'::bpchar)
-> Subquery Scan b (cost=1027.15..1027.16 rows=1 width=8)
-> Aggregate (cost=1027.15..1027.15 rows=1 width=0)
-> Nested Loop (cost=0.00..1027.14 rows=3 width=0)
-> Seq Scan on datasources ds
(cost=0.00..4.44 rows=2 width=4)
Filter: ((sourcetype)::text =
'foreclosure'::text)
-> Index Scan using
idx_properties_datasourcestateauctiondate on properties p
(cost=0.00..511.32 rows=3 width=4)
Index Cond: (p.datasource =
"outer".sourceid)
Filter: ((countystate = 'GA'::bpchar)
AND ((status = 0) OR (status = 1) OR (status = 2)) AND ((lastreviewed2
>= (('now'::text)::timestamp(6) with time zone - '14 days'::interval))
OR (status = 1) OR (status = 2)))
-> Subquery Scan c (cost=1666.51..1666.52 rows=1 width=8)
-> Aggregate (cost=1666.51..1666.51 rows=1 width=0)
-> Index Scan using properties_idx_search on properties
p (cost=0.00..1666.46 rows=18 width=0)
Index Cond: (countystate = 'GA'::bpchar)
Filter: ((datasource = 1087) AND ((status = 1) OR
(status = 2)))
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query Tuning
2004-10-08 14:49 Query Tuning Pallav Kalva <pkalva@deg.cc>
@ 2004-10-08 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 17:29 ` Re: Query Tuning Pallav Kalva <pkalva@deg.cc>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-10-08 16:42 UTC (permalink / raw)
To: Pallav Kalva <pkalva@deg.cc>; +Cc: pgsql-performance
Pallav Kalva <pkalva@deg.cc> writes:
> I have a problem with the below query, when i do explain on the
> below query on my live database it doesnt use any index specified on the
> tables and does seq scan on the table which is 400k records. But if i
> copy the same table onto a different database on a different machine it
> uses all the indexes specified and query runs much quicker.
It looks to me like you've never vacuumed/analyzed the copy, and so you
get a different plan there. The fact that that plan is better than the
one made with statistics is unhappy making :-( ... but when you only
show us EXPLAIN output rather than EXPLAIN ANALYZE, it's impossible to
speculate about why. Also, what PG version is this?
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query Tuning
2004-10-08 14:49 Query Tuning Pallav Kalva <pkalva@deg.cc>
2004-10-08 16:42 ` Re: Query Tuning Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-08 17:29 ` Pallav Kalva <pkalva@deg.cc>
0 siblings, 0 replies; 11+ messages in thread
From: Pallav Kalva @ 2004-10-08 17:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>Pallav Kalva <pkalva@deg.cc> writes:
>
>
>> I have a problem with the below query, when i do explain on the
>>below query on my live database it doesnt use any index specified on the
>>tables and does seq scan on the table which is 400k records. But if i
>>copy the same table onto a different database on a different machine it
>>uses all the indexes specified and query runs much quicker.
>>
>>
>
>It looks to me like you've never vacuumed/analyzed the copy, and so you
>get a different plan there. The fact that that plan is better than the
>one made with statistics is unhappy making :-( ... but when you only
>show us EXPLAIN output rather than EXPLAIN ANALYZE, it's impossible to
>speculate about why. Also, what PG version is this?
>
> regards, tom lane
>
>
>
Thanks! for the quick reply. I cant run the EXPLAIN ANALYZE on the live
database because, it takes lot of time and hols up lot of other queries
on the table. The postgres version I am using is 7.4 . when you say " i
never vacuum/analyxed the copy" you mean the Live database ? or the copy
of the live database ? . I run vacuum database daily on my live database
as a part of daily maintanence.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Query tuning
@ 2006-08-23 00:46 Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 01:37 ` Re: Query tuning Chris <dmagick@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Subbiah, Stalin @ 2006-08-23 00:46 UTC (permalink / raw)
To: pgsql-performance
Hello All,
This query runs forever and ever. Nature of this table being lots of
inserts/deletes/query, I vacuum it every half hour to keep the holes
reusable and nightly once vacuum analyze to update the optimizer. We've
got index on eventtime only. Running it for current day uses index range
scan and it runs within acceptable time. Below is the explain of the
query. Is the order by sequencenum desc prevents from applying limit
optimization?
explain SELECT *
FROM EVENTLOG
WHERE EVENTTIME>'07/23/06 16:00:00'
AND EVENTTIME<'08/22/06 16:00:00'
AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=15546930.29..15546931.54 rows=500 width=327)
-> Sort (cost=15546930.29..15581924.84 rows=13997819 width=327)
Sort Key: eventtime, sequencenum
-> Seq Scan on eventlog (cost=0.00..2332700.25 rows=13997819
width=327)
Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
(5 rows)
Thanks,
Stalin
Pg version 8.0.1, suse 64bit.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query tuning
2006-08-23 00:46 Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
@ 2006-08-23 01:37 ` Chris <dmagick@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Chris @ 2006-08-23 01:37 UTC (permalink / raw)
To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: pgsql-performance
Subbiah, Stalin wrote:
> Hello All,
>
> This query runs forever and ever. Nature of this table being lots of
> inserts/deletes/query, I vacuum it every half hour to keep the holes
> reusable and nightly once vacuum analyze to update the optimizer. We've
> got index on eventtime only. Running it for current day uses index range
> scan and it runs within acceptable time. Below is the explain of the
> query. Is the order by sequencenum desc prevents from applying limit
> optimization?
>
> explain SELECT *
> FROM EVENTLOG
> WHERE EVENTTIME>'07/23/06 16:00:00'
> AND EVENTTIME<'08/22/06 16:00:00'
> AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
>
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> -------------------------------------------------------------
> Limit (cost=15546930.29..15546931.54 rows=500 width=327)
> -> Sort (cost=15546930.29..15581924.84 rows=13997819 width=327)
> Sort Key: eventtime, sequencenum
> -> Seq Scan on eventlog (cost=0.00..2332700.25 rows=13997819
> width=327)
> Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> (5 rows)
>
> Thanks,
> Stalin
> Pg version 8.0.1, suse 64bit.
Firstly you should update to 8.0.8 - because it's in the same stream you
won't need to do a dump/initdb/reload like a major version change, it
should be a simple upgrade.
Can you send explain analyze instead of just explain?
It sounds like you're not analyz'ing enough - if you're doing lots of
updates/deletes/inserts then the statistics postgresql uses to choose
whether to do an index scan or something else will quickly be outdated
and so it'll have to go back to a full table scan every time..
Can you set up autovacuum to handle that for you more regularly?
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query tuning
@ 2006-08-23 02:53 Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 03:05 ` Re: Query tuning Chris <dmagick@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Subbiah, Stalin @ 2006-08-23 02:53 UTC (permalink / raw)
To: pgsql-performance
Actually these servers will be upgraded to 8.1.4 in couple of months.
Here you go with explain analyze.
# explain analyze SELECT *
FROM EVENTLOG
WHERE EVENTTIME>'07/23/06 16:00:00' AND EVENTTIME<'08/22/06 16:00:00'
AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------------------------------------------
Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
time=427771.568..427772.904 rows=500 loops=1)
-> Sort (cost=15583108.89..15618188.88 rows=14031998 width=327)
(actual time=427770.504..427771.894 rows=1000 loops=1)
Sort Key: eventtime, sequencenum
-> Seq Scan on eventlog (cost=0.00..2334535.17 rows=14031998
width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
without time zone) AND (((objdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
Total runtime: 437884.134 ms
(6 rows)
-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]
Sent: Tuesday, August 22, 2006 6:37 PM
To: Subbiah, Stalin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query tuning
Subbiah, Stalin wrote:
> Hello All,
>
> This query runs forever and ever. Nature of this table being lots of
> inserts/deletes/query, I vacuum it every half hour to keep the holes
> reusable and nightly once vacuum analyze to update the optimizer.
> We've got index on eventtime only. Running it for current day uses
> index range scan and it runs within acceptable time. Below is the
> explain of the query. Is the order by sequencenum desc prevents from
> applying limit optimization?
>
> explain SELECT *
> FROM EVENTLOG
> WHERE EVENTTIME>'07/23/06 16:00:00'
> AND EVENTTIME<'08/22/06 16:00:00'
> AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 0;
>
> QUERY PLAN
>
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> -------------------------------------------------------------
> Limit (cost=15546930.29..15546931.54 rows=500 width=327)
> -> Sort (cost=15546930.29..15581924.84 rows=13997819 width=327)
> Sort Key: eventtime, sequencenum
> -> Seq Scan on eventlog (cost=0.00..2332700.25
> rows=13997819
> width=327)
> Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> (5 rows)
>
> Thanks,
> Stalin
> Pg version 8.0.1, suse 64bit.
Firstly you should update to 8.0.8 - because it's in the same stream you
won't need to do a dump/initdb/reload like a major version change, it
should be a simple upgrade.
Can you send explain analyze instead of just explain?
It sounds like you're not analyz'ing enough - if you're doing lots of
updates/deletes/inserts then the statistics postgresql uses to choose
whether to do an index scan or something else will quickly be outdated
and so it'll have to go back to a full table scan every time..
Can you set up autovacuum to handle that for you more regularly?
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query tuning
2006-08-23 02:53 Re: Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
@ 2006-08-23 03:05 ` Chris <dmagick@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Chris @ 2006-08-23 03:05 UTC (permalink / raw)
To: Subbiah, Stalin <SSubbiah@netopia.com>; +Cc: pgsql-performance
Subbiah, Stalin wrote:
> Actually these servers will be upgraded to 8.1.4 in couple of months.
even so, you could get some bad data in there.
http://www.postgresql.org/docs/8.0/static/release.html . Go through the
old release notes and you'll find various race conditions, crashes etc.
> Here you go with explain analyze.
>
> # explain analyze SELECT *
> FROM EVENTLOG
> WHERE EVENTTIME>'07/23/06 16:00:00' AND EVENTTIME<'08/22/06 16:00:00'
> AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
>
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> -------------------------------------------------------------
> Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> time=427771.568..427772.904 rows=500 loops=1)
> -> Sort (cost=15583108.89..15618188.88 rows=14031998 width=327)
> (actual time=427770.504..427771.894 rows=1000 loops=1)
> Sort Key: eventtime, sequencenum
> -> Seq Scan on eventlog (cost=0.00..2334535.17 rows=14031998
> width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> Total runtime: 437884.134 ms
> (6 rows)
If you analyze the table then run this again what plan does it come back
with?
I can't read explain output properly but I suspect (and I'm sure I'll be
corrected if need be) that the sort step is way out of whack and so is
the seq scan because the stats aren't up to date enough.
Do you have an index on objdomainid, objid and userdomainid (one index
per field) ? I wonder if that will help much.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query tuning
@ 2006-08-23 18:02 Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 23:19 ` Re: Query tuning Dave Dutcher <dave@tridecap.com>
0 siblings, 1 reply; 11+ messages in thread
From: Subbiah, Stalin @ 2006-08-23 18:02 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
I get the same plan after running vacuum analyze. Nope, I don't have
index on objdomainid, objid and userdomainid. Only eventime has it.
-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]
Sent: Tuesday, August 22, 2006 8:06 PM
To: Subbiah, Stalin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query tuning
Subbiah, Stalin wrote:
> Actually these servers will be upgraded to 8.1.4 in couple of months.
even so, you could get some bad data in there.
http://www.postgresql.org/docs/8.0/static/release.html . Go through the
old release notes and you'll find various race conditions, crashes etc.
> Here you go with explain analyze.
>
> # explain analyze SELECT *
> FROM EVENTLOG
> WHERE EVENTTIME>'07/23/06 16:00:00' AND EVENTTIME<'08/22/06 16:00:00'
> AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
>
> QUERY PLAN
>
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> ----------------------------------------------------------------------
> --
> -------------------------------------------------------------
> Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> time=427771.568..427772.904 rows=500 loops=1)
> -> Sort (cost=15583108.89..15618188.88 rows=14031998 width=327)
> (actual time=427770.504..427771.894 rows=1000 loops=1)
> Sort Key: eventtime, sequencenum
> -> Seq Scan on eventlog (cost=0.00..2334535.17
> rows=14031998
> width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> Filter: ((eventtime > '2006-07-23 16:00:00'::timestamp
> without time zone) AND (eventtime < '2006-08-22 16:00:00'::timestamp
> without time zone) AND (((objdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> Total runtime: 437884.134 ms
> (6 rows)
If you analyze the table then run this again what plan does it come back
with?
I can't read explain output properly but I suspect (and I'm sure I'll be
corrected if need be) that the sort step is way out of whack and so is
the seq scan because the stats aren't up to date enough.
Do you have an index on objdomainid, objid and userdomainid (one index
per field) ? I wonder if that will help much.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Query tuning
2006-08-23 18:02 Re: Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
@ 2006-08-23 23:19 ` Dave Dutcher <dave@tridecap.com>
0 siblings, 0 replies; 11+ messages in thread
From: Dave Dutcher @ 2006-08-23 23:19 UTC (permalink / raw)
To: 'Subbiah, Stalin' <SSubbiah@netopia.com>; +Cc: pgsql-performance
It seems to me that what would work best is an index scan backward on the
eventtime index. I don't see why that wouldn't work for you, maybe the
planner is just esitmating the seq scan and sort is faster for some reason.
What does EXPLAIN say if you use a small limit and offset like 10? Or what
does EXPLAIN say if you first run "set enable_seqscan=false;" (If you get
the same plan, then I wouldn't bother running EXPLAIN ANALYZE, but if you
get a different plan I would run EXPLAIN ANALYZE to see if the new plan is
any faster.)
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Subbiah, Stalin
> Sent: Wednesday, August 23, 2006 1:03 PM
> To: Chris
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
>
> I get the same plan after running vacuum analyze. Nope, I don't have
> index on objdomainid, objid and userdomainid. Only eventime has it.
>
> -----Original Message-----
> From: Chris [mailto:dmagick@gmail.com]
> Sent: Tuesday, August 22, 2006 8:06 PM
> To: Subbiah, Stalin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
> Subbiah, Stalin wrote:
> > Actually these servers will be upgraded to 8.1.4 in couple
> of months.
>
> even so, you could get some bad data in there.
> http://www.postgresql.org/docs/8.0/static/release.html . Go
> through the
> old release notes and you'll find various race conditions,
> crashes etc.
>
> > Here you go with explain analyze.
> >
> > # explain analyze SELECT *
> > FROM EVENTLOG
> > WHERE EVENTTIME>'07/23/06 16:00:00' AND
> EVENTTIME<'08/22/06 16:00:00'
>
> > AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> > ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
> >
> > QUERY PLAN
> >
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> > -------------------------------------------------------------
> > Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> > time=427771.568..427772.904 rows=500 loops=1)
> > -> Sort (cost=15583108.89..15618188.88 rows=14031998
> width=327)
> > (actual time=427770.504..427771.894 rows=1000 loops=1)
> > Sort Key: eventtime, sequencenum
> > -> Seq Scan on eventlog (cost=0.00..2334535.17
> > rows=14031998
> > width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> > Filter: ((eventtime > '2006-07-23
> 16:00:00'::timestamp
> > without time zone) AND (eventtime < '2006-08-22
> 16:00:00'::timestamp
> > without time zone) AND (((objdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> > Total runtime: 437884.134 ms
> > (6 rows)
>
> If you analyze the table then run this again what plan does
> it come back
> with?
>
> I can't read explain output properly but I suspect (and I'm
> sure I'll be
> corrected if need be) that the sort step is way out of whack and so is
> the seq scan because the stats aren't up to date enough.
>
> Do you have an index on objdomainid, objid and userdomainid (one index
> per field) ? I wonder if that will help much.
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] Query tuning
@ 2006-08-24 04:44 Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-24 05:30 ` Re: [PERFORM] Query tuning Jason Minion <jason.minion@sigler.com>
0 siblings, 1 reply; 11+ messages in thread
From: Subbiah, Stalin @ 2006-08-24 04:44 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
Changing limit or offset to a small number doesn't have any change in
plans. Likewise enable_seqscan to false. They still take 8-10 mins to
runs.
-----Original Message-----
From: Dave Dutcher [mailto:dave@tridecap.com]
Sent: Wednesday, August 23, 2006 4:20 PM
To: Subbiah, Stalin
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Query tuning
It seems to me that what would work best is an index scan backward on
the eventtime index. I don't see why that wouldn't work for you, maybe
the planner is just esitmating the seq scan and sort is faster for some
reason.
What does EXPLAIN say if you use a small limit and offset like 10? Or
what does EXPLAIN say if you first run "set enable_seqscan=false;" (If
you get the same plan, then I wouldn't bother running EXPLAIN ANALYZE,
but if you get a different plan I would run EXPLAIN ANALYZE to see if
the new plan is any faster.)
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Subbiah,
> Stalin
> Sent: Wednesday, August 23, 2006 1:03 PM
> To: Chris
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
>
> I get the same plan after running vacuum analyze. Nope, I don't have
> index on objdomainid, objid and userdomainid. Only eventime has it.
>
> -----Original Message-----
> From: Chris [mailto:dmagick@gmail.com]
> Sent: Tuesday, August 22, 2006 8:06 PM
> To: Subbiah, Stalin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
> Subbiah, Stalin wrote:
> > Actually these servers will be upgraded to 8.1.4 in couple
> of months.
>
> even so, you could get some bad data in there.
> http://www.postgresql.org/docs/8.0/static/release.html . Go through
> the old release notes and you'll find various race conditions, crashes
> etc.
>
> > Here you go with explain analyze.
> >
> > # explain analyze SELECT *
> > FROM EVENTLOG
> > WHERE EVENTTIME>'07/23/06 16:00:00' AND
> EVENTTIME<'08/22/06 16:00:00'
>
> > AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> > ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
> >
> > QUERY PLAN
> >
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> > -------------------------------------------------------------
> > Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> > time=427771.568..427772.904 rows=500 loops=1)
> > -> Sort (cost=15583108.89..15618188.88 rows=14031998
> width=327)
> > (actual time=427770.504..427771.894 rows=1000 loops=1)
> > Sort Key: eventtime, sequencenum
> > -> Seq Scan on eventlog (cost=0.00..2334535.17
> > rows=14031998
> > width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> > Filter: ((eventtime > '2006-07-23
> 16:00:00'::timestamp
> > without time zone) AND (eventtime < '2006-08-22
> 16:00:00'::timestamp
> > without time zone) AND (((objdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> > Total runtime: 437884.134 ms
> > (6 rows)
>
> If you analyze the table then run this again what plan does it come
> back with?
>
> I can't read explain output properly but I suspect (and I'm sure I'll
> be corrected if need be) that the sort step is way out of whack and so
> is the seq scan because the stats aren't up to date enough.
>
> Do you have an index on objdomainid, objid and userdomainid (one index
> per field) ? I wonder if that will help much.
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [PERFORM] Query tuning
2006-08-24 04:44 Re: [PERFORM] Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
@ 2006-08-24 05:30 ` Jason Minion <jason.minion@sigler.com>
0 siblings, 0 replies; 11+ messages in thread
From: Jason Minion @ 2006-08-24 05:30 UTC (permalink / raw)
To: Subbiah, Stalin <SSubbiah@netopia.com>; Dave Dutcher <dave@tridecap.com>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
You really need to index the three fields you are restricting with your
select query (OBJDOMAINID, OBJID and USERDOMAINID). Depending on whether
or not you have other queries that filter for one of the three fields
but not the others, you might want to have separate indexes across each
of the fields. Also, if those types are not CHAR/VARCHAR/TEXT/similar,
try casting your values to the types for those fields (ie,
OBJDOMAINID='somethinghere'::WVARCHAR, and should be much less necessary
with pgsql v8+). Since you don't have indexes on those fields, the only
thing the query planner can do is a full table scan and for each record
check the field values. With indexes it will be able to filter by those
values first and then sort the remaining values. On the other hand, if
most of your records have the same values for your filter fields (ie
only a handful of values dispersed amongst millions of rows) then you
may be back to the seq scan.
Jason Minion
jason.minion@sigler.com
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Subbiah, Stalin
Sent: Wednesday, August 23, 2006 11:44 PM
To: Dave Dutcher
Cc: pgsql-performance@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] [PERFORM] Query tuning
Changing limit or offset to a small number doesn't have any change in
plans. Likewise enable_seqscan to false. They still take 8-10 mins to
runs.
-----Original Message-----
From: Dave Dutcher [mailto:dave@tridecap.com]
Sent: Wednesday, August 23, 2006 4:20 PM
To: Subbiah, Stalin
Cc: pgsql-performance@postgresql.org
Subject: RE: [PERFORM] Query tuning
It seems to me that what would work best is an index scan backward on
the eventtime index. I don't see why that wouldn't work for you, maybe
the planner is just esitmating the seq scan and sort is faster for some
reason.
What does EXPLAIN say if you use a small limit and offset like 10? Or
what does EXPLAIN say if you first run "set enable_seqscan=false;" (If
you get the same plan, then I wouldn't bother running EXPLAIN ANALYZE,
but if you get a different plan I would run EXPLAIN ANALYZE to see if
the new plan is any faster.)
> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Subbiah,
> Stalin
> Sent: Wednesday, August 23, 2006 1:03 PM
> To: Chris
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
>
> I get the same plan after running vacuum analyze. Nope, I don't have
> index on objdomainid, objid and userdomainid. Only eventime has it.
>
> -----Original Message-----
> From: Chris [mailto:dmagick@gmail.com]
> Sent: Tuesday, August 22, 2006 8:06 PM
> To: Subbiah, Stalin
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query tuning
>
> Subbiah, Stalin wrote:
> > Actually these servers will be upgraded to 8.1.4 in couple
> of months.
>
> even so, you could get some bad data in there.
> http://www.postgresql.org/docs/8.0/static/release.html . Go through
> the old release notes and you'll find various race conditions, crashes
> etc.
>
> > Here you go with explain analyze.
> >
> > # explain analyze SELECT *
> > FROM EVENTLOG
> > WHERE EVENTTIME>'07/23/06 16:00:00' AND
> EVENTTIME<'08/22/06 16:00:00'
>
> > AND (OBJDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR OBJID='tzRh39d0d91luNGT1weIUjLvFIcA'
> > OR USERDOMAINID='tzRh39d0d91luNGT1weIUjLvFIcA')
> > ORDER BY EVENTTIME DESC, SEQUENCENUM DESC LIMIT 500 OFFSET 500;
> >
> > QUERY PLAN
> >
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> >
> ----------------------------------------------------------------------
> > --
> > -------------------------------------------------------------
> > Limit (cost=15583110.14..15583111.39 rows=500 width=327) (actual
> > time=427771.568..427772.904 rows=500 loops=1)
> > -> Sort (cost=15583108.89..15618188.88 rows=14031998
> width=327)
> > (actual time=427770.504..427771.894 rows=1000 loops=1)
> > Sort Key: eventtime, sequencenum
> > -> Seq Scan on eventlog (cost=0.00..2334535.17
> > rows=14031998
> > width=327) (actual time=10.370..190038.764 rows=7699388 loops=1)
> > Filter: ((eventtime > '2006-07-23
> 16:00:00'::timestamp
> > without time zone) AND (eventtime < '2006-08-22
> 16:00:00'::timestamp
> > without time zone) AND (((objdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((objid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text) OR ((userdomainid)::text =
> > 'tzRh39d0d91luNGT1weIUjLvFIcA'::text)))
> > Total runtime: 437884.134 ms
> > (6 rows)
>
> If you analyze the table then run this again what plan does it come
> back with?
>
> I can't read explain output properly but I suspect (and I'm sure I'll
> be corrected if need be) that the sort step is way out of whack and so
> is the seq scan because the stats aren't up to date enough.
>
> Do you have an index on objdomainid, objid and userdomainid (one index
> per field) ? I wonder if that will help much.
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-08-24 05:30 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-08 14:49 Query Tuning Pallav Kalva <pkalva@deg.cc>
2004-10-08 16:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 17:29 ` Pallav Kalva <pkalva@deg.cc>
2006-08-23 00:46 Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 01:37 ` Re: Query tuning Chris <dmagick@gmail.com>
2006-08-23 02:53 Re: Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 03:05 ` Re: Query tuning Chris <dmagick@gmail.com>
2006-08-23 18:02 Re: Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-23 23:19 ` Re: Query tuning Dave Dutcher <dave@tridecap.com>
2006-08-24 04:44 Re: [PERFORM] Query tuning Subbiah, Stalin <SSubbiah@netopia.com>
2006-08-24 05:30 ` Re: [PERFORM] Query tuning Jason Minion <jason.minion@sigler.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