pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple join doesn't use index
23+ messages / 8 participants
[nested] [flat]
* Simple join doesn't use index
@ 2013-01-03 22:54 Alex Vinnik <alvinnik.g@gmail.com>
2013-01-03 23:11 ` Re: Simple join doesn't use index Jeremy Harris <jgh@wizmail.org>
2013-01-04 04:33 ` Re: Simple join doesn't use index Stefan Andreatta <s.andreatta@synedra.com>
2013-01-08 00:13 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-09 04:34 ` Re: Simple join doesn't use index Jeff Janes <jeff.janes@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
0 siblings, 5 replies; 23+ messages in thread
From: Alex Vinnik @ 2013-01-03 22:54 UTC (permalink / raw)
To: pgsql-performance
Hi everybody,
I have implemented my first app using PG DB and thought for a minute(may be
two) that I know something about PG but below problem totally destroyed my
confidence :). Please help me to restore it.
Here is simple join query. It runs just fine on MS SQL 2008 and uses
all available indexes using even bigger overall dataset.
select visits.id, views.id
from visits join views on visits.id = views.visit_id
where visits.created_at >= '11/15/2012' and visits.created_at <
'11/16/2012'
Quick performance stat
MS SQL: 1 second, 264K rows
PG: 158 seconds, 264K rows
Explain plan from both DBs
PG QUERY PLAN
Hash Join (cost=12716.17..1101820.09 rows=248494 width=8)
Hash Cond: (views.visit_id = visits.id)
-> Seq Scan on views (cost=0.00..819136.56 rows=17434456 width=8)
-> Hash (cost=10549.16..10549.16 rows=132081 width=4)
-> Index Scan using visits_created_at_index on visits
(cost=0.00..10549.16 rows=132081 width=4)
Index Cond: ((created_at >= '2012-11-15 00:00:00'::timestamp
without time zone) AND (created_at < '2012-11-16 00:00:00'::timestamp
without time zone))
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+---------------------------------+------------+------------------------------------------------------------------------------------------
public | views | views_pkey | |
CREATE UNIQUE INDEX views_pkey ON views USING btree (id)
public | views | views_visit_id_index | |
CREATE INDEX views_visit_id_index ON views USING btree (visit_id)
MS SQL Query plan
'11/16/2012'
|--Parallelism(Gather Streams)
|--Nested Loops(Inner Join, OUTER REFERENCES:([visits].[id],
[Expr1006]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Index Seek(OBJECT:([visits].[test]),
SEEK:([visits].[created_at] >= '2012-11-15 00:00:00.000' AND
[visits].[created_at] < '2012-11-16 00:00:00.000') ORDERED FORWARD)
|--Index Seek(OBJECT:([views].[views_visit_id_index]),
SEEK:([views].[visit_id]=[raw_visits].[id]) ORDERED FORWARD)
It is clear that PG does full table scan "Seq Scan on views
(cost=0.00..819136.56 rows=17434456 width=8)"
Don't understand why PG doesn't use views_visit_id_index in that query but
rather scans whole table. One explanation I have found that when resulting
dataset constitutes ~15% of total number of rows in the table then seq scan
is used. In this case resulting dataset is just 1.5% of total number of
rows. So it must be something different. Any reason why it happens and how
to fix it?
Postgres 9.2
Ubuntu 12.04.1 LTS
shared_buffers = 4GB the rest of the settings are default ones
Thanks
-Alex
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-03 23:11 ` Jeremy Harris <jgh@wizmail.org>
4 siblings, 0 replies; 23+ messages in thread
From: Jeremy Harris @ 2013-01-03 23:11 UTC (permalink / raw)
To: pgsql-performance
On 01/03/2013 10:54 PM, Alex Vinnik wrote:
> I have implemented my first app using PG DB and thought for a minute(may be
> two) that I know something about PG but below problem totally destroyed my
> confidence :). Please help me to restore it.
https://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Jeremy
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-04 04:33 ` Stefan Andreatta <s.andreatta@synedra.com>
4 siblings, 0 replies; 23+ messages in thread
From: Stefan Andreatta @ 2013-01-04 04:33 UTC (permalink / raw)
To: pgsql-performance
On 01/03/2013 11:54 PM, Alex Vinnik wrote:
> Don't understand why PG doesn't use views_visit_id_index in that query
> but rather scans whole table. One explanation I have found that when
> resulting dataset constitutes ~15% of total number of rows in the table
> then seq scan is used. In this case resulting dataset is just 1.5% of
> total number of rows. So it must be something different. Any reason why
> it happens and how to fix it?
But does the query planner know the same? If you added the EXPLAIN
ANALYZE output of the query and something like:
SELECT tablename AS table_name, attname AS column_name,
null_frac, avg_width, n_distinct, correlation
FROM pg_stats
WHERE tablename in ('views', 'visits');
.. one could possibly tell a bit more.
> Postgres 9.2
> Ubuntu 12.04.1 LTS
> shared_buffers = 4GB the rest of the settings are default ones
There are more than just this one memory related value, that need to be
changed for optimal performance. E.g. effective_cache_size can have a
direct effect on use of nested loops. See:
http://www.postgresql.org/docs/9.2/static/runtime-config-query.html
Regards,
Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-08 00:13 ` Merlin Moncure <mmoncure@gmail.com>
4 siblings, 0 replies; 23+ messages in thread
From: Merlin Moncure @ 2013-01-08 00:13 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Thu, Jan 3, 2013 at 4:54 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> Don't understand why PG doesn't use views_visit_id_index in that query but
> rather scans whole table. One explanation I have found that when resulting
> dataset constitutes ~15% of total number of rows in the table then seq scan
> is used. In this case resulting dataset is just 1.5% of total number of
> rows. So it must be something different. Any reason why it happens and how
> to fix it?
>
> Postgres 9.2
> Ubuntu 12.04.1 LTS
> shared_buffers = 4GB the rest of the settings are default ones
<snip>
It happens because you lied to the database...heh. In particular, the
'effective_cache_size' setting which defaults to 128mb. That probably
needs to be much, much larger. Basically postgres is figuring the
cache is much smaller than the data and starts to favor sequential
plans once you hit a certain threshold. If you had a server with only
say 256mb ram, it probably *would* be faster.
SQL server probably uses all kinds of crazy native unportable kernel
calls to avoid having to make a similar .conf setting. Or maybe it
just assumes infinite cache size...dunno.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-09 04:34 ` Jeff Janes <jeff.janes@gmail.com>
4 siblings, 0 replies; 23+ messages in thread
From: Jeff Janes @ 2013-01-09 04:34 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Thursday, January 3, 2013, Alex Vinnik wrote:
> Hi everybody,
>
> I have implemented my first app using PG DB and thought for a minute(may
> be two) that I know something about PG but below
> problem totally destroyed my confidence :). Please help me to restore it.
>
> Here is simple join query. It runs just fine on MS SQL 2008 and uses
> all available indexes using even bigger overall dataset.
>
> select visits.id, views.id
> from visits join views on visits.id = views.visit_id
> where visits.created_at >= '11/15/2012' and visits.created_at <
> '11/16/2012'
>
> Quick performance stat
>
> MS SQL: 1 second, 264K rows
>
If it returns 264K rows in 1 second, then it must have all data in memory.
Which prompts a few questions:
Is *all* data in memory, or is it just the data needed for this particular
query because you already ran it recently with the same date range?
> PG: 158 seconds, 264K rows
>
Does the PG machine have enough memory to hold all the data in RAM? If so,
does it actually have all the data in RAM? That is, is the cache already
warm? Starting from scratch it can take a long time for the cache to warm
up naturally. And finally, if all the data is in RAM, does PG know this?
For the last question, the answer is "no", since you are using default
settings. You need to lower random_page_cost and probably also
seq_page_cost in order to trick PG to think the data is in RAM. Of course
if you do this when the data is in fact not in RAM, the result could be
catastrophically bad plans. (And when I tried to replicate your situation
on anemic hardware, indeed the hash join often was faster than the nested
loop on both indexes.)
>
> Explain plan from both DBs
>
> PG QUERY PLAN
> Hash Join (cost=12716.17..1101820.09 rows=248494 width=8)
> Hash Cond: (views.visit_id = visits.id)
> -> Seq Scan on views (cost=0.00..819136.56 rows=17434456 width=8)
>
This cost estimate is probably due mostly to seg_page_cost and
cpu_tuple_cost, which at their defaults means the table has 645,000 blocks
(819136 - 17434456/100) blocks and each block has ~30 rows.
But you are returning 248,494 rows, or roughly 1 / 2.5 of a row per block.
Let's say you need to fetch 200,000 blocks (in random order) to get those
rows. Since at default settings fetching 200,000 random blocks is
considered as expensive as fetching 800,000 sequential blocks, the index
scan you want already looks more expensive than the sequential scan. But,
if you want to use the index scan, you also have to fetch the index
blocks, which a sequential scan does not need to do. There are probably
about 50,000 index blocks, but each one has to be fetched about 5 times
(248,494/50,000). Because your effective_cache_size is so low, PG assumes
the next time it needs to fetch the same block, it will no longer be in
memory and so needs to be fetched again at full random_page_cost.
> It is clear that PG does full table scan "Seq Scan on views
> (cost=0.00..819136.56 rows=17434456 width=8)"
>
> Don't understand why PG doesn't use views_visit_id_index in that query but
> rather scans whole table. One explanation I have found that when resulting
> dataset constitutes ~15% of total number of rows in the table then seq scan
> is used.
>
I don't know where you found that rule of thumb, but it would probably more
accurate if it was given in in terms of the percentage of the table's
*blocks* scanned, rather than *rows*.
> In this case resulting dataset is just 1.5% of total number of rows.
>
Since there are about 30 rows per block, scanning 1.5% of the rows means
scanning somewhat less than 45% of the blocks, assuming the rows are
randomly distributed over the blocks. And they are scanned in a less
efficient way.
> Postgres 9.2
>
You are probably getting hit hard by the overly-large "fudge factor"
penalty for scans of large indexes, of much discussion recently in regards
to 9.2.
> Ubuntu 12.04.1 LTS
> shared_buffers = 4GB the rest of the settings are default ones
>
The default effective_cache_size is almost certainly wrong, and if the
analogy to MSSQL to is correct, then random_page_cost almost certainly is
as well.
Another poster referred you to the wiki page for suggestion on how to
report slow queries, particularly using EXPLAIN (analyze, buffers) rather
than merely EXPLAIN. In this case, I would also try setting
enable_hashjoin=off and enable_mergejoin=off in the session, in order to
force the planner to use the plan you think you want, so we can see what PG
thinks of that one.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-09 15:49 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
4 siblings, 1 reply; 23+ messages in thread
From: Alex Vinnik @ 2013-01-09 15:49 UTC (permalink / raw)
To: pgsql-performance
Guys, thanks a lot for your input. It is very valuable for us. We plan to
fix a separate dev server similar to production one, copy all data there
and try you suggestions as we really don't want to do it on production
server. I also noticed that IOPS jumps to 100% when running this query. So
it is a bit scary to make those changes in production directly. Will report
back on the progress and findings.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-09 15:53 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Merlin Moncure @ 2013-01-09 15:53 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Wed, Jan 9, 2013 at 9:49 AM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> Guys, thanks a lot for your input. It is very valuable for us. We plan to
> fix a separate dev server similar to production one, copy all data there and
> try you suggestions as we really don't want to do it on production server. I
> also noticed that IOPS jumps to 100% when running this query. So it is a bit
> scary to make those changes in production directly. Will report back on the
> progress and findings.
nothing wrong with that, but keep in mind you can tweak
'effective_cache_size' for a single session with 'set' command;
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
@ 2013-01-28 23:43 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 02:31 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-29 20:06 ` Re: Simple join doesn't use index Jeff Janes <jeff.janes@gmail.com>
2013-02-02 16:39 ` Re: Simple join doesn't use index Jeff Janes <jeff.janes@gmail.com>
0 siblings, 4 replies; 23+ messages in thread
From: Alex Vinnik @ 2013-01-28 23:43 UTC (permalink / raw)
To: pgsql-performance
It sure turned out that default settings are not a good fit. Setting
random_page_cost
to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
being used in explain plan and IO utilization is close to 0.
QUERY PLAN
Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
time=2176.045..2418.162 rows=241238 loops=1)
Sort Key: visits.id, views.id
Sort Method: external sort Disk: 4248kB
-> Nested Loop (cost=0.00..950554.81 rows=200575 width=8) (actual
time=0.048..1735.357 rows=241238 loops=1)
-> Index Scan using visits_created_at_index on visits
(cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..178.591
rows=136021 loops=1)
Index Cond: ((created_at >= '2012-12-15 00:00:00'::timestamp
without time zone) AND (created_at < '2012-12-16 00:00:00'::timestamp
without time zone))
-> Index Scan using views_visit_id_index on views
(cost=0.00..11.33 rows=12 width=8) (actual time=0.004..0.006 rows=2
loops=136021)
Index Cond: (visit_id = visits.id)
Total runtime: 2635.169 ms
However I noticed that sorting is done using disk("external sort Disk:
4248kB") which prompted me to take a look at work_mem. But it turned out
that small increase to 4MB from default 1MB turns off index usage and query
gets x10 slower. IO utilization jumped to 100% from literally nothing. so
back to square one...
QUERY PLAN
Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual
time=33200.762..33474.443 rows=241238 loops=1)
Sort Key: visits.id, views.id
Sort Method: external merge Disk: 4248kB
-> Hash Join (cost=6491.17..917410.33 rows=200575 width=8) (actual
time=7156.498..32723.221 rows=241238 loops=1)
Hash Cond: (views.visit_id = visits.id)
-> Seq Scan on views (cost=0.00..832189.95 rows=8768395 width=8)
(actual time=0.100..12126.342 rows=8200704 loops=1)
-> Hash (cost=5459.16..5459.16 rows=82561 width=4) (actual
time=353.683..353.683 rows=136021 loops=1)
Buckets: 16384 Batches: 2 (originally 1) Memory Usage:
4097kB
-> Index Scan using visits_created_at_index on visits
(cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..175.051
rows=136021 loops=1)
Index Cond: ((created_at >= '2012-12-15
00:00:00'::timestamp without time zone) AND (created_at < '2012-12-16
00:00:00'::timestamp without time zone))
Total runtime: 33698.000 ms
Basically PG is going through all views again and not using "Index Scan
using views_visit_id_index on views". Looks like setting work_mem confuses
planner somehow. Any idea what can be done to do sorting in memory. I
suspect it should make query even more faster. Thanks -Alex
> nothing wrong with that, but keep in mind you can tweak
> 'effective_cache_size' for a single session with 'set' command;
>
> merlin
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 00:55 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 23:15 ` Re: Simple join doesn't use index Jeff Janes <jeff.janes@gmail.com>
3 siblings, 2 replies; 23+ messages in thread
From: Filip Rembiałkowski @ 2013-01-29 00:55 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> It sure turned out that default settings are not a good fit.
>
do you know pgtune?
it's a good tool for starters, if you want a fast postgres and don't really
want to learn what's behind the scenes.
random_page_cost=1 might be not what you really want.
it would mean that random reads are as fast as as sequential reads, which
probably is true only for SSD
Filip
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
@ 2013-01-29 14:24 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 16:19 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 17:39 ` Re: Simple join doesn't use index Ben Chobot <bench@silentmedia.com>
1 sibling, 2 replies; 23+ messages in thread
From: Alex Vinnik @ 2013-01-29 14:24 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski <plk.zuber@gmail.com>wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
>
>> It sure turned out that default settings are not a good fit.
>>
>
> do you know pgtune?
> it's a good tool for starters, if you want a fast postgres and don't
> really want to learn what's behind the scenes.
>
Yeah.. I came across pgtune but noticed that latest version dated
2009-10-29 http://pgfoundry.org/frs/?group_id=1000416 which is kind of
outdated. Tar file has settings for pg 8.3. Is still relevant?
>
> random_page_cost=1 might be not what you really want.
> it would mean that random reads are as fast as as sequential reads, which
> probably is true only for SSD
>
What randon_page_cost would be more appropriate for EC2 EBS Provisioned
volume that can handle 2,000 IOPS?
>
>
>
> Filip
>
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 16:19 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2013-02-04 21:14 ` Re: Simple join doesn't use index Dan Fairs <dan.fairs@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Filip Rembiałkowski @ 2013-01-29 16:19 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Tue, Jan 29, 2013 at 8:24 AM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> On Mon, Jan 28, 2013 at 6:55 PM, Filip Rembiałkowski <plk.zuber@gmail.com>wrote:
>
>>
>> do you know pgtune?
>> it's a good tool for starters, if you want a fast postgres and don't
>> really want to learn what's behind the scenes.
>>
> Yeah.. I came across pgtune but noticed that latest version dated
> 2009-10-29 http://pgfoundry.org/frs/?group_id=1000416 which is kind of
> outdated. Tar file has settings for pg 8.3. Is still relevant?
>
Yes, I'm sure it will not do anything bad to your config.
>
>> random_page_cost=1 might be not what you really want.
>> it would mean that random reads are as fast as as sequential reads, which
>> probably is true only for SSD
>>
> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
> volume that can handle 2,000 IOPS?
>
>>
>>
I'd say: don't guess. Measure.
Use any tool that can test sequential disk block reads versus random disk
block reads.
bonnie++ is quite popular.
Filip
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 16:19 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
@ 2013-02-04 21:14 ` Dan Fairs <dan.fairs@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Dan Fairs @ 2013-02-04 21:14 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: Alex Vinnik <alvinnik.g@gmail.com>; pgsql-performance
> Yeah.. I came across pgtune but noticed that latest version dated 2009-10-29 http://pgfoundry.org/frs/?group_id=1000416 which is kind of outdated. Tar file has settings for pg 8.3. Is still relevant?
>
> Yes, I'm sure it will not do anything bad to your config.
>
Apologies for leaping in a little late, but I note the version on Github has been updated much more recently:
https://github.com/gregs1104/pgtune
Cheers,
Dan
--
Dan Fairs | dan.fairs@gmail.com | @danfairs | secondsync.com
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 17:39 ` Ben Chobot <bench@silentmedia.com>
2013-01-29 18:59 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Ben Chobot @ 2013-01-29 17:39 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-performance
On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
> random_page_cost=1 might be not what you really want.
> it would mean that random reads are as fast as as sequential reads, which probably is true only for SSD
> What randon_page_cost would be more appropriate for EC2 EBS Provisioned volume that can handle 2,000 IOPS?
For EC2 Provisioned IOPS volumes - not standard EBS - random_page_cost=1 is exactly what you want.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 17:39 ` Re: Simple join doesn't use index Ben Chobot <bench@silentmedia.com>
@ 2013-01-29 18:59 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 19:35 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alex Vinnik @ 2013-01-29 18:59 UTC (permalink / raw)
To: Ben Chobot <bench@silentmedia.com>; +Cc: Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-performance
On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot <bench@silentmedia.com> wrote:
> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
>
> random_page_cost=1 might be not what you really want.
>> it would mean that random reads are as fast as as sequential reads, which
>> probably is true only for SSD
>>
> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
> volume that can handle 2,000 IOPS?
>
>
> For EC2 Provisioned IOPS volumes - not standard EBS - random_page_cost=1
> is exactly what you want.
>
> Well... after some experimentation it turned out that random_page_cost=0.6
gives me fast query
QUERY PLAN
Sort (cost=754114.96..754510.46 rows=158199 width=8) (actual
time=1839.324..2035.405 rows=209401 loops=1)
Sort Key: visits.id, views.id
Sort Method: quicksort Memory: 15960kB
-> Nested Loop (cost=0.00..740453.38 rows=158199 width=8) (actual
time=0.048..1531.592 rows=209401 loops=1)
-> Index Scan using visits_created_at_index on visits
(cost=0.00..5929.82 rows=115492 width=4) (actual time=0.032..161.488
rows=131311 loops=1)
Index Cond: ((created_at >= '2013-01-15 00:00:00'::timestamp
without time zone) AND (created_at < '2013-01-16 00:00:00'::timestamp
without time zone))
-> Index Scan using views_visit_id_index on views
(cost=0.00..6.26 rows=10 width=8) (actual time=0.003..0.005 rows=2
loops=131311)
Index Cond: (visit_id = visits.id)
Total runtime: 2234.142 ms
random_page_cost=0.7 slows it down 16 times
Sort (cost=804548.42..804943.92 rows=158199 width=8) (actual
time=37011.337..37205.449 rows=209401 loops=1)
Sort Key: visits.id, views.id
Sort Method: quicksort Memory: 15960kB
-> Merge Join (cost=15871.37..790886.85 rows=158199 width=8) (actual
time=35673.602..36714.056 rows=209401 loops=1)
Merge Cond: (visits.id = views.visit_id)
-> Sort (cost=15824.44..16113.17 rows=115492 width=4) (actual
time=335.486..463.085 rows=131311 loops=1)
Sort Key: visits.id
Sort Method: quicksort Memory: 12300kB
-> Index Scan using visits_created_at_index on visits
(cost=0.00..6113.04 rows=115492 width=4) (actual time=0.034..159.326
rows=131311 loops=1)
Index Cond: ((created_at >= '2013-01-15
00:00:00'::timestamp without time zone) AND (created_at < '2013-01-16
00:00:00'::timestamp without time zone))
-> Index Scan using views_visit_id_visit_buoy_index on views
(cost=0.00..757596.22 rows=6122770 width=8) (actual time=0.017..30765.316
rows=5145902 loops=1)
Total runtime: 37407.174 ms
I am totally puzzled now...
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 17:39 ` Re: Simple join doesn't use index Ben Chobot <bench@silentmedia.com>
2013-01-29 18:59 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 19:35 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-29 20:48 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Merlin Moncure @ 2013-01-29 19:35 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: Ben Chobot <bench@silentmedia.com>; Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-performance
On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
>
>
>
> On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot <bench@silentmedia.com> wrote:
>>
>> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
>>
>>> random_page_cost=1 might be not what you really want.
>>> it would mean that random reads are as fast as as sequential reads, which
>>> probably is true only for SSD
>>
>> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
>> volume that can handle 2,000 IOPS?
>>
>>
>> For EC2 Provisioned IOPS volumes - not standard EBS - random_page_cost=1
>> is exactly what you want.
>>
> Well... after some experimentation it turned out that random_page_cost=0.6
> gives me fast query
>
> QUERY PLAN
> Sort (cost=754114.96..754510.46 rows=158199 width=8) (actual
> time=1839.324..2035.405 rows=209401 loops=1)
> Sort Key: visits.id, views.id
> Sort Method: quicksort Memory: 15960kB
> -> Nested Loop (cost=0.00..740453.38 rows=158199 width=8) (actual
> time=0.048..1531.592 rows=209401 loops=1)
> -> Index Scan using visits_created_at_index on visits
> (cost=0.00..5929.82 rows=115492 width=4) (actual time=0.032..161.488
> rows=131311 loops=1)
> Index Cond: ((created_at >= '2013-01-15 00:00:00'::timestamp
> without time zone) AND (created_at < '2013-01-16 00:00:00'::timestamp
> without time zone))
> -> Index Scan using views_visit_id_index on views (cost=0.00..6.26
> rows=10 width=8) (actual time=0.003..0.005 rows=2 loops=131311)
> Index Cond: (visit_id = visits.id)
> Total runtime: 2234.142 ms
>
> random_page_cost=0.7 slows it down 16 times
>
> Sort (cost=804548.42..804943.92 rows=158199 width=8) (actual
> time=37011.337..37205.449 rows=209401 loops=1)
> Sort Key: visits.id, views.id
> Sort Method: quicksort Memory: 15960kB
> -> Merge Join (cost=15871.37..790886.85 rows=158199 width=8) (actual
> time=35673.602..36714.056 rows=209401 loops=1)
> Merge Cond: (visits.id = views.visit_id)
> -> Sort (cost=15824.44..16113.17 rows=115492 width=4) (actual
> time=335.486..463.085 rows=131311 loops=1)
> Sort Key: visits.id
> Sort Method: quicksort Memory: 12300kB
> -> Index Scan using visits_created_at_index on visits
> (cost=0.00..6113.04 rows=115492 width=4) (actual time=0.034..159.326
> rows=131311 loops=1)
> Index Cond: ((created_at >= '2013-01-15
> 00:00:00'::timestamp without time zone) AND (created_at < '2013-01-16
> 00:00:00'::timestamp without time zone))
> -> Index Scan using views_visit_id_visit_buoy_index on views
> (cost=0.00..757596.22 rows=6122770 width=8) (actual time=0.017..30765.316
> rows=5145902 loops=1)
Something is awry here. pg is doing an index scan via
views_visit_id_visit_buoy_index with no matching condition. What's
the definition of that index? The reason why the random_page_cost
adjustment is working is that you are highly penalizing sequential
type scans so that the database is avoiding the merge (sort A, sort B,
stepwise compare).
SQL server is doing a nestloop/index scan, just like the faster pg
plan, but is a bit faster because it's parallelizing.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 17:39 ` Re: Simple join doesn't use index Ben Chobot <bench@silentmedia.com>
2013-01-29 18:59 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 19:35 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
@ 2013-01-29 20:48 ` Alex Vinnik <alvinnik.g@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Alex Vinnik @ 2013-01-29 20:48 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Ben Chobot <bench@silentmedia.com>; Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-performance
index definition
CREATE INDEX views_visit_id_visit_buoy_index ON views USING btree
(visit_id, visit_buoy)
On Tue, Jan 29, 2013 at 1:35 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Jan 29, 2013 at 12:59 PM, Alex Vinnik <alvinnik.g@gmail.com>
> wrote:
> >
> >
> >
> > On Tue, Jan 29, 2013 at 11:39 AM, Ben Chobot <bench@silentmedia.com>
> wrote:
> >>
> >> On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:
> >>
> >>> random_page_cost=1 might be not what you really want.
> >>> it would mean that random reads are as fast as as sequential reads,
> which
> >>> probably is true only for SSD
> >>
> >> What randon_page_cost would be more appropriate for EC2 EBS Provisioned
> >> volume that can handle 2,000 IOPS?
> >>
> >>
> >> For EC2 Provisioned IOPS volumes - not standard EBS - random_page_cost=1
> >> is exactly what you want.
> >>
> > Well... after some experimentation it turned out that
> random_page_cost=0.6
> > gives me fast query
> >
> > QUERY PLAN
> > Sort (cost=754114.96..754510.46 rows=158199 width=8) (actual
> > time=1839.324..2035.405 rows=209401 loops=1)
> > Sort Key: visits.id, views.id
> > Sort Method: quicksort Memory: 15960kB
> > -> Nested Loop (cost=0.00..740453.38 rows=158199 width=8) (actual
> > time=0.048..1531.592 rows=209401 loops=1)
> > -> Index Scan using visits_created_at_index on visits
> > (cost=0.00..5929.82 rows=115492 width=4) (actual time=0.032..161.488
> > rows=131311 loops=1)
> > Index Cond: ((created_at >= '2013-01-15
> 00:00:00'::timestamp
> > without time zone) AND (created_at < '2013-01-16 00:00:00'::timestamp
> > without time zone))
> > -> Index Scan using views_visit_id_index on views
> (cost=0.00..6.26
> > rows=10 width=8) (actual time=0.003..0.005 rows=2 loops=131311)
> > Index Cond: (visit_id = visits.id)
> > Total runtime: 2234.142 ms
> >
> > random_page_cost=0.7 slows it down 16 times
> >
> > Sort (cost=804548.42..804943.92 rows=158199 width=8) (actual
> > time=37011.337..37205.449 rows=209401 loops=1)
> > Sort Key: visits.id, views.id
> > Sort Method: quicksort Memory: 15960kB
> > -> Merge Join (cost=15871.37..790886.85 rows=158199 width=8) (actual
> > time=35673.602..36714.056 rows=209401 loops=1)
> > Merge Cond: (visits.id = views.visit_id)
> > -> Sort (cost=15824.44..16113.17 rows=115492 width=4) (actual
> > time=335.486..463.085 rows=131311 loops=1)
> > Sort Key: visits.id
> > Sort Method: quicksort Memory: 12300kB
> > -> Index Scan using visits_created_at_index on visits
> > (cost=0.00..6113.04 rows=115492 width=4) (actual time=0.034..159.326
> > rows=131311 loops=1)
> > Index Cond: ((created_at >= '2013-01-15
> > 00:00:00'::timestamp without time zone) AND (created_at < '2013-01-16
> > 00:00:00'::timestamp without time zone))
>
> > -> Index Scan using views_visit_id_visit_buoy_index on views
> > (cost=0.00..757596.22 rows=6122770 width=8) (actual time=0.017..30765.316
> > rows=5145902 loops=1)
>
> Something is awry here. pg is doing an index scan via
> views_visit_id_visit_buoy_index with no matching condition. What's
> the definition of that index? The reason why the random_page_cost
> adjustment is working is that you are highly penalizing sequential
> type scans so that the database is avoiding the merge (sort A, sort B,
> stepwise compare).
>
> SQL server is doing a nestloop/index scan, just like the faster pg
> plan, but is a bit faster because it's parallelizing.
>
> merlin
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Re: Simple join doesn't use index Filip Rembiałkowski <plk.zuber@gmail.com>
@ 2013-01-29 23:15 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 23+ messages in thread
From: Jeff Janes @ 2013-01-29 23:15 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: Alex Vinnik <alvinnik.g@gmail.com>; pgsql-performance
On Mon, Jan 28, 2013 at 4:55 PM, Filip Rembiałkowski
<plk.zuber@gmail.com> wrote:
>
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
>>
>> It sure turned out that default settings are not a good fit.
>
>
> do you know pgtune?
> it's a good tool for starters, if you want a fast postgres and don't really
> want to learn what's behind the scenes.
>
> random_page_cost=1 might be not what you really want.
> it would mean that random reads are as fast as as sequential reads, which
> probably is true only for SSD
Or that the "reads" are cached and coming from RAM, which is almost
surely the case here.
Cheers,
Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 02:31 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-29 14:41 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
3 siblings, 1 reply; 23+ messages in thread
From: Merlin Moncure @ 2013-01-29 02:31 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> It sure turned out that default settings are not a good fit. Setting
> random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
> that indexes are being used in explain plan and IO utilization is close to
> 0.
>
> QUERY PLAN
> Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
> time=2176.045..2418.162 rows=241238 loops=1)
> Sort Key: visits.id, views.id
> Sort Method: external sort Disk: 4248kB
> -> Nested Loop (cost=0.00..950554.81 rows=200575 width=8) (actual
> time=0.048..1735.357 rows=241238 loops=1)
> -> Index Scan using visits_created_at_index on visits
> (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..178.591
> rows=136021 loops=1)
> Index Cond: ((created_at >= '2012-12-15 00:00:00'::timestamp
> without time zone) AND (created_at < '2012-12-16 00:00:00'::timestamp
> without time zone))
> -> Index Scan using views_visit_id_index on views
> (cost=0.00..11.33 rows=12 width=8) (actual time=0.004..0.006 rows=2
> loops=136021)
> Index Cond: (visit_id = visits.id)
> Total runtime: 2635.169 ms
>
> However I noticed that sorting is done using disk("external sort Disk:
> 4248kB") which prompted me to take a look at work_mem. But it turned out
> that small increase to 4MB from default 1MB turns off index usage and query
> gets x10 slower. IO utilization jumped to 100% from literally nothing. so
> back to square one...
>
> QUERY PLAN
> Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual
> time=33200.762..33474.443 rows=241238 loops=1)
> Sort Key: visits.id, views.id
> Sort Method: external merge Disk: 4248kB
> -> Hash Join (cost=6491.17..917410.33 rows=200575 width=8) (actual
> time=7156.498..32723.221 rows=241238 loops=1)
> Hash Cond: (views.visit_id = visits.id)
> -> Seq Scan on views (cost=0.00..832189.95 rows=8768395 width=8)
> (actual time=0.100..12126.342 rows=8200704 loops=1)
> -> Hash (cost=5459.16..5459.16 rows=82561 width=4) (actual
> time=353.683..353.683 rows=136021 loops=1)
> Buckets: 16384 Batches: 2 (originally 1) Memory Usage:
> 4097kB
> -> Index Scan using visits_created_at_index on visits
> (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..175.051
> rows=136021 loops=1)
> Index Cond: ((created_at >= '2012-12-15
> 00:00:00'::timestamp without time zone) AND (created_at < '2012-12-16
> 00:00:00'::timestamp without time zone))
> Total runtime: 33698.000 ms
>
> Basically PG is going through all views again and not using "Index Scan
> using views_visit_id_index on views". Looks like setting work_mem confuses
> planner somehow. Any idea what can be done to do sorting in memory. I
> suspect it should make query even more faster. Thanks -Alex
hm, what happens when you set work_mem a fair amount higher? (say,
64mb). You can set it for one session by going "set work_mem='64mb';
" as opposed to the entire server in postgresql.conf.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 02:31 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
@ 2013-01-29 14:41 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 16:41 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alex Vinnik @ 2013-01-29 14:41 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
Setting work_mem to 64MB triggers in memory sort but look what happens with
views look up. PG goes through all records there "Seq Scan on views"
instead of using visitor_id index and I have only subset of real data to
play around. Can imagine what cost would be running it against bigger
dataset. Something else is in play here that makes planner to take this
route. Any ideas how to gain more insight into planner's inner workings?
QUERY PLAN
Sort (cost=960280.46..960844.00 rows=225414 width=8) (actual
time=23328.040..23537.126 rows=209401 loops=1)
Sort Key: visits.id, views.id
Sort Method: quicksort Memory: 15960kB
-> Hash Join (cost=8089.16..940238.66 rows=225414 width=8) (actual
time=6622.072..22995.890 rows=209401 loops=1)
Hash Cond: (views.visit_id = visits.id)
-> Seq Scan on views (cost=0.00..831748.05 rows=8724205 width=8)
(actual time=0.093..10552.306 rows=6995893 loops=1)
-> Hash (cost=6645.51..6645.51 rows=115492 width=4) (actual
time=307.389..307.389 rows=131311 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 4617kB
-> Index Scan using visits_created_at_index on visits
(cost=0.00..6645.51 rows=115492 width=4) (actual time=0.040..163.151
rows=131311 loops=1)
Index Cond: ((created_at >= '2013-01-15
00:00:00'::timestamp without time zone) AND (created_at < '2013-01-16
00:00:00'::timestamp without time zone))
Total runtime: 23733.045 ms
On Mon, Jan 28, 2013 at 8:31 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Mon, Jan 28, 2013 at 5:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> > It sure turned out that default settings are not a good fit. Setting
> > random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly
> see
> > that indexes are being used in explain plan and IO utilization is close
> to
> > 0.
> >
> > QUERY PLAN
> > Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
> > time=2176.045..2418.162 rows=241238 loops=1)
> > Sort Key: visits.id, views.id
> > Sort Method: external sort Disk: 4248kB
> > -> Nested Loop (cost=0.00..950554.81 rows=200575 width=8) (actual
> > time=0.048..1735.357 rows=241238 loops=1)
> > -> Index Scan using visits_created_at_index on visits
> > (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..178.591
> > rows=136021 loops=1)
> > Index Cond: ((created_at >= '2012-12-15
> 00:00:00'::timestamp
> > without time zone) AND (created_at < '2012-12-16 00:00:00'::timestamp
> > without time zone))
> > -> Index Scan using views_visit_id_index on views
> > (cost=0.00..11.33 rows=12 width=8) (actual time=0.004..0.006 rows=2
> > loops=136021)
> > Index Cond: (visit_id = visits.id)
> > Total runtime: 2635.169 ms
> >
> > However I noticed that sorting is done using disk("external sort Disk:
> > 4248kB") which prompted me to take a look at work_mem. But it turned out
> > that small increase to 4MB from default 1MB turns off index usage and
> query
> > gets x10 slower. IO utilization jumped to 100% from literally nothing. so
> > back to square one...
> >
> > QUERY PLAN
> > Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual
> > time=33200.762..33474.443 rows=241238 loops=1)
> > Sort Key: visits.id, views.id
> > Sort Method: external merge Disk: 4248kB
> > -> Hash Join (cost=6491.17..917410.33 rows=200575 width=8) (actual
> > time=7156.498..32723.221 rows=241238 loops=1)
> > Hash Cond: (views.visit_id = visits.id)
> > -> Seq Scan on views (cost=0.00..832189.95 rows=8768395
> width=8)
> > (actual time=0.100..12126.342 rows=8200704 loops=1)
> > -> Hash (cost=5459.16..5459.16 rows=82561 width=4) (actual
> > time=353.683..353.683 rows=136021 loops=1)
> > Buckets: 16384 Batches: 2 (originally 1) Memory Usage:
> > 4097kB
> > -> Index Scan using visits_created_at_index on visits
> > (cost=0.00..5459.16 rows=82561 width=4) (actual time=0.032..175.051
> > rows=136021 loops=1)
> > Index Cond: ((created_at >= '2012-12-15
> > 00:00:00'::timestamp without time zone) AND (created_at < '2012-12-16
> > 00:00:00'::timestamp without time zone))
> > Total runtime: 33698.000 ms
> >
> > Basically PG is going through all views again and not using "Index Scan
> > using views_visit_id_index on views". Looks like setting work_mem
> confuses
> > planner somehow. Any idea what can be done to do sorting in memory. I
> > suspect it should make query even more faster. Thanks -Alex
>
> hm, what happens when you set work_mem a fair amount higher? (say,
> 64mb). You can set it for one session by going "set work_mem='64mb';
> " as opposed to the entire server in postgresql.conf.
>
> merlin
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 02:31 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-29 14:41 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 16:41 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Merlin Moncure @ 2013-01-29 16:41 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Tue, Jan 29, 2013 at 8:41 AM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> Setting work_mem to 64MB triggers in memory sort but look what happens with
> views look up. PG goes through all records there "Seq Scan on views" instead
> of using visitor_id index and I have only subset of real data to play
> around. Can imagine what cost would be running it against bigger dataset.
> Something else is in play here that makes planner to take this route. Any
> ideas how to gain more insight into planner's inner workings?
did you set effective_cache_seize as noted upthread?
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-01-29 20:06 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-29 20:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
3 siblings, 1 reply; 23+ messages in thread
From: Jeff Janes @ 2013-01-29 20:06 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Mon, Jan 28, 2013 at 3:43 PM, Alex Vinnik <alvinnik.g@gmail.com> wrote:
> It sure turned out that default settings are not a good fit. Setting
> random_page_cost to 1.0 made query to run in 2.6 seconds and I clearly see
> that indexes are being used in explain plan and IO utilization is close to
> 0.
>
> QUERY PLAN
> Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
> time=2176.045..2418.162 rows=241238 loops=1)
> Sort Key: visits.id, views.id
> Sort Method: external sort Disk: 4248kB
What query are you running? The query you originally showed us should
not be doing this sort in the first place.
Cheers,
Jeff
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 20:06 ` Re: Simple join doesn't use index Jeff Janes <jeff.janes@gmail.com>
@ 2013-01-29 20:43 ` Alex Vinnik <alvinnik.g@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Alex Vinnik @ 2013-01-29 20:43 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On Tue, Jan 29, 2013 at 2:06 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> > Sort Key: visits.id, views.id
> > Sort Method: external sort Disk: 4248kB
>
> What query are you running? The query you originally showed us should
> not be doing this sort in the first place.
>
> Cheers,
>
> Jeff
>
Here is the query
select visits.id, views.id
from visits join views on visits.id = views.visit_id
where visits.created_at >= '01/15/2013' and visits.created_at < '01/16/2013'
order by visits.id, views.id;
Original query didn't have order by clause
Here query plan w/o order by
Merge Join (cost=18213.46..802113.80 rows=182579 width=8) (actual
time=144443.693..145469.499 rows=209401 loops=1)
Merge Cond: (visits.id = views.visit_id)
-> Sort (cost=18195.47..18523.91 rows=131373 width=4) (actual
time=335.496..464.929 rows=131311 loops=1)
Sort Key: visits.id
Sort Method: quicksort Memory: 12300kB
-> Index Scan using visits_created_at_index on visits
(cost=0.00..7026.59 rows=131373 width=4) (actual time=0.037..162.047
rows=131311 loops=1)
Index Cond: ((created_at >= '2013-01-15 00:00:00'::timestamp
without time zone) AND (created_at < '2013-01-16 00:00:00'::timestamp
without time zone))
-> Index Scan using views_visit_id_visit_buoy_index on views
(cost=0.00..766120.99 rows=6126002 width=8) (actual
time=18.960..140565.130 rows=4014837 loops=1)
Total runtime: 145664.274 ms
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Simple join doesn't use index
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:49 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Re: Simple join doesn't use index Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Re: Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
@ 2013-02-02 16:39 ` Jeff Janes <jeff.janes@gmail.com>
3 siblings, 0 replies; 23+ messages in thread
From: Jeff Janes @ 2013-02-02 16:39 UTC (permalink / raw)
To: Alex Vinnik <alvinnik.g@gmail.com>; +Cc: pgsql-performance
On Monday, January 28, 2013, Alex Vinnik wrote:
> It sure turned out that default settings are not a good fit. Setting random_page_cost
> to 1.0 made query to run in 2.6 seconds and I clearly see that indexes are
> being used in explain plan and IO utilization is close to 0.
>
This is not surprising. The default settings are aimed at quite small
servers, while you seem to have a rather substantial one. Have you done
anything yet to fix effective_cache_size?
> QUERY PLAN
> Sort (cost=969787.23..970288.67 rows=200575 width=8) (actual
> time=2176.045..2418.162 rows=241238 loops=1)
>
However I noticed that sorting is done using disk("external sort Disk:
> 4248kB")
>
As far as pgsql is concerned, it is using the disk. But the kernel is
probably buffering that to an extent that the disk is probably being
touched rather little. So I wouldn't worry about it.
> which prompted me to take a look at work_mem. But it turned out that
> small increase to 4MB from default 1MB turns off index usage and query gets
> x10 slower. IO utilization jumped to 100% from literally nothing. so back
> to square one...
>
> QUERY PLAN
> Sort (cost=936642.75..937144.19 rows=200575 width=8) (actual
> time=33200.762..33474.443 rows=241238 loops=1)
>
And why should the IO utilization have jumped? Is everything in memory, or
is it not? You should run your EXPLAINs with (analyze, buffers), and also
you should turn on track_io_timings, at least in the local session; that
will give us some insights.
If everything is in memory, then why is the seq scan taking so long? If
not, then why is the nested loop such a good idea? (In my hands, when
everything does *not* fit in memory, the nested loop is very very bad)
You seem have a bit of an infatuation with Dec 15th, running that one query
over and over and over. Why? If the real
live query is not just for that one day repeatedly, then you should test
with different days, not just one day repeatedly. (And if your real query
really is like the movie "Groundhog Day", you should probably cluster or
partition with that in mind.)
Anyway, there was an issue introduced in 9.2.0 and to be removed in 9.2.3
which over-penalized nested loops that had large indexes on the inner side.
Since your different plans are so close to each other in estimated cost, I
think this issue would be enough to tip it into the seq scan. Also, your
poor setting of effective_cache_size might also be enough to tip it. And
both combined, almost certainly are.
But ultimately, I think you are optimizing for a case that does not
actually exist.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2013-02-04 21:14 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-03 22:54 Simple join doesn't use index Alex Vinnik <alvinnik.g@gmail.com>
2013-01-03 23:11 ` Jeremy Harris <jgh@wizmail.org>
2013-01-04 04:33 ` Stefan Andreatta <s.andreatta@synedra.com>
2013-01-08 00:13 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-09 04:34 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-09 15:49 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-09 15:53 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-28 23:43 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 00:55 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2013-01-29 14:24 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 16:19 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2013-02-04 21:14 ` Dan Fairs <dan.fairs@gmail.com>
2013-01-29 17:39 ` Ben Chobot <bench@silentmedia.com>
2013-01-29 18:59 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 19:35 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-29 20:48 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 23:15 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-29 02:31 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-29 14:41 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-01-29 16:41 ` Merlin Moncure <mmoncure@gmail.com>
2013-01-29 20:06 ` Jeff Janes <jeff.janes@gmail.com>
2013-01-29 20:43 ` Alex Vinnik <alvinnik.g@gmail.com>
2013-02-02 16:39 ` Jeff Janes <jeff.janes@gmail.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