Re: [PERFORM] ZFS vs. UFS
On 24.07.2012 14:51, Laszlo Nagy wrote: * UFS is not journaled. There is journal support for UFS as far as i know. Please have a look at the gjournal manpage. > Yes, but gjournal works for disk devices. That isn't completly correct! gjournal works with all GEOM-devices, which could be not only disk devices, but also (remote) disk devices, (remote) files, (remote) software-raids etc. It is very easy to mirror the *complete* disk from one *server* to another. I use this technic for customers which need cheap backups of their complete server. But a RAID card will be much faster than this. I just wanted to make this clear. Greetings, Torsten -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] transactions start time
On 24 July 2012 20:21, Richard Huxton wrote:
> On 24/07/12 12:14, Aleksei Arefjev wrote:
>
>> Hi,
>>
>> In statistical reports gathered by PgBadger on our PostgreSQL databases
>> almost always we have in "Queries that took up the most time" report
>> table information about transactions start time ('BEGIN;' command).
>> Something like that in example below:
>>
>> 23h34m52.26s48,556,1670.00sBEGIN;
>>
>> 0.82s | BEGIN;
>> 0.82s | BEGIN;
>> 0.82s | BEGIN;
>> 0.81s | BEGIN;
>> 0.81s | BEGIN;
>> 0.81s | BEGIN;
>> 0.80s | BEGIN;
>> 0.80s | BEGIN;
>> 0.79s | BEGIN;
>> 0.79s | BEGIN;
>>
>
> I'm not sure if I'm reading this right, but are there more than 48 million
> BEGINs that took 0s each (presumably rounded down) and then a handful
> taking about 0.8s?
>
> If so, then it's likely nothing to do with the BEGIN and just that the
> machine was busy doing other things when you started a transaction.
>
>
> Databases placed on different hardware, OS - Debian GNU/Linux,
>> PostgreSQL 9.1
>>
>> So, questions are:
>> 1. Is this a normal situation with transactions start time ( BEGIN
>> method) ?
>>
>
> See above
>
>
> 2. How can we reduce transactions start time if it's possible in
>> principle?
>>
>
> Below 0.00? Probably not
>
>
> 3. What happens in PostgreSQL on transaction starting time? Can someone
>> describe this process in detail? (of course, I saw in PostgreSQL source
>> code, for example, definition such kind functions, like StartTransaction
>> function, but it's not so easy to understand for third-party researcher,
>> that all of these operations mean in real for performance)
>>
>
> Well there are two important things to understand:
> 1. All* commands run in a transaction
> 2. I think most of the work in getting a new snapshot etc gets pushed back
> until it's needed.
>
If so, maybe using of 'SET TRANSACTION SNAPSHOT' command with the
pre-existing transaction exported snapshot by the pg_export_snapshot
function could be usefull for reducing transactions start time -
http://www.postgresql.org/docs/9.2/static/sql-set-transaction.html
>
> So - the overall impact of issuing BEGIN should be close to zero.
>
> --
> Richard Huxton
> Archonet Ltd
>
Re: [PERFORM] Using ctid column changes plan drastically
Tom Lane, 24.07.2012 19:12: Well, it would only help if you're running a PG version that's new enough to recognize the NOT EXISTS as an anti-join; and even then, it's possible that joining on a tid column forecloses enough plan types that you don't get any real benefit. But I'm just guessing. Can you show exactly what you tried and what EXPLAIN ANALYZE results you got? I am using 9.1.4 (as I said in my initial post). I finally found a solution that runs fine: DELETE FROM dupes a WHERE EXISTS (SELECT 1 FROM dupes b WHERE b.first_name = a.first_name AND b.last_name = a.last_name AND b.ctid > a.ctid); The execution plan for this is: Delete on public.dupes a (cost=14575.95..16978.87 rows=25000 width=12) (actual time=2419.334..2419.334 rows=0 loops=1) Buffers: shared hit=18029 -> Merge Semi Join (cost=14575.95..16978.87 rows=25000 width=12) (actual time=2043.674..2392.707 rows=17097 loops=1) Output: a.ctid, b.ctid Merge Cond: ((a.first_name = b.first_name) AND (a.last_name = b.last_name)) Join Filter: (b.ctid > a.ctid) Buffers: shared hit=930 -> Sort (cost=7287.98..7475.48 rows=75000 width=20) (actual time=1024.195..1030.051 rows=75000 loops=1) Output: a.ctid, a.first_name, a.last_name Sort Key: a.first_name, a.last_name Sort Method: quicksort Memory: 8870kB Buffers: shared hit=465 -> Seq Scan on public.dupes a (cost=0.00..1215.00 rows=75000 width=20) (actual time=0.025..23.234 rows=75000 loops=1) Output: a.ctid, a.first_name, a.last_name Buffers: shared hit=465 -> Sort (cost=7287.98..7475.48 rows=75000 width=20) (actual time=1019.148..1028.483 rows=105841 loops=1) Output: b.ctid, b.first_name, b.last_name Sort Key: b.first_name, b.last_name Sort Method: quicksort Memory: 8870kB Buffers: shared hit=465 -> Seq Scan on public.dupes b (cost=0.00..1215.00 rows=75000 width=20) (actual time=0.017..19.133 rows=75000 loops=1) Output: b.ctid, b.first_name, b.last_name Buffers: shared hit=465 Total runtime: 2420.953 ms Which is a lot better than the plan using "WHERE ctid NOT IN (.)": Delete on public.dupes (cost=1777.50..4925055.00 rows=37500 width=6) (actual time=582515.094..582515.094 rows=0 loops=1) Buffers: shared hit=18027 -> Seq Scan on public.dupes (cost=1777.50..4925055.00 rows=37500 width=6) (actual time=1038.164..582332.927 rows=17097 loops=1) Output: dupes.ctid Filter: (NOT (SubPlan 1)) Buffers: shared hit=930 SubPlan 1 -> Materialize (cost=1777.50..1890.00 rows=7500 width=20) (actual time=0.001..2.283 rows=35552 loops=75000) Output: (min(b.ctid)), b.first_name, b.last_name Buffers: shared hit=465 -> HashAggregate (cost=1777.50..1852.50 rows=7500 width=20) (actual time=90.964..120.228 rows=57903 loops=1) Output: min(b.ctid), b.first_name, b.last_name Buffers: shared hit=465 -> Seq Scan on public.dupes b (cost=0.00..1215.00 rows=75000 width=20) (actual time=0.008..25.515 rows=75000 loops=1) Output: b.ctid, b.first_name, b.last_name Buffers: shared hit=465 Total runtime: 582517.711 ms Using "WHERE id NOT IN (...)" is the fastest way: Delete on public.dupes (cost=1871.25..3273.75 rows=37500 width=6) (actual time=187.949..187.949 rows=0 loops=1) Buffers: shared hit=18490 -> Seq Scan on public.dupes (cost=1871.25..3273.75 rows=37500 width=6) (actual time=125.351..171.108 rows=17097 loops=1) Output: dupes.ctid Filter: (NOT (hashed SubPlan 1)) Buffers: shared hit=930 SubPlan 1 -> HashAggregate (cost=1777.50..1852.50 rows=7500 width=18) (actual time=73.131..93.421 rows=57903 loops=1) Output: min(b.id), b.first_name, b.last_name Buffers: shared hit=465 -> Seq Scan on public.dupes b (cost=0.00..1215.00 rows=75000 width=18) (actual time=0.004..8.515 rows=75000 loops=1) Output: b.id, b.first_name, b.last_name Buffers: shared hit=465 Total runtime: 189.222 ms Regards Thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do I need more time with partition table?
Thanks. I missed to add the trigger. Now I added it, but still without partition taking less time compared to with partition query. *With partition :- * explain analyze > select * > from table1 as c > inner join table2 as a on c.crmid = a.activityid and deleted = 0 > where module ='Leads' > ; > > > QUERY PLAN > > > -- > Hash Join (cost=25669.79..86440.88 rows=288058 width=367) (actual > time=4411.734..4411.734 rows=0 loops=1) >Hash Cond: (a.activityid = c.crmid) >-> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.264..1336.555 rows=681434 loops=1) >-> Hash (cost=13207.07..13207.07 rows=288058 width=228) (actual > time=1457.495..1457.495 rows=287365 loops=1) > Buckets: 1024 Batches: 128 Memory Usage: 226kB > -> Append (cost=0.00..13207.07 rows=288058 width=228) (actual > time=0.014..1000.182 rows=287365 loops=1) >-> Seq Scan on table1 c (cost=0.00..0.00 rows=1 > width=367) (actual time=0.001..0.001 rows=0 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) >-> Seq Scan on table1_leads c (cost=0.00..13207.07 > rows=288057 width=228) (actual time=0.010..490.169 rows=287365 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > Total runtime: 4412.534 ms > (11 rows) *Without partition :- * explain analyze > select * > from table1_old as c > inner join table2 as a on c.crmid = a.activityid and deleted = 0 > where module ='Leads' > ; > > QUERY PLAN > > > -- > Hash Join (cost=92095.07..157111.03 rows=107445 width=502) (actual > time=3795.273..3795.273 rows=0 loops=1) >Hash Cond: (a.activityid = c.crmid) >-> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.030..812.925 rows=681434 loops=1) >-> Hash (cost=73246.44..73246.44 rows=314850 width=363) (actual > time=1377.624..1377.624 rows=287365 loops=1) > Buckets: 1024 Batches: 128 Memory Usage: 226kB > -> Bitmap Heap Scan on table1_old c (cost=9228.69..73246.44 > rows=314850 width=363) (actual time=83.189..926.542 rows=287365 loops=1) >Recheck Cond: (((module)::text = 'Leads'::text) AND > (deleted = 0)) >-> Bitmap Index Scan on crmentity_module_idx > (cost=0.00..9149.98 rows=314850 width=0) (actual time=79.357..79.357 > rows=287365 loops=1) > Index Cond: ((module)::text = 'Leads'::text) > Total runtime: 3795.721 ms > (10 rows) On Tue, Jul 24, 2012 at 5:46 PM, Jan Otto wrote: > hi al, > > > With Parition :- > > > > > > explain analyze > > select * > > from table1 as c > > inner join table2 as a on c.crmid = a.table2id and deleted = 0 > > where module ='Leads'; > > > QUERY PLAN > > > -- > > Hash Join (cost=108101.50..175252.57 rows=313256 width=506) (actual > time=8430.588..8430.588 rows=0 loops=1) > >Hash Cond: (a.table2id = c.crmid) > >-> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.054..870.554 rows=681434 loops=1) > >-> Hash (cost=89195.80..89195.80 rows=313256 width=367) (actual > time=2751.950..2751.950 rows=287365 loops=1) > > Buckets: 1024 Batches: 128 Memory Usage: 226kB > > -> Append (cost=0.00..89195.80 rows=313256 width=367) (actual > time=0.034..2304.191 rows=287365 loops=1) > >-> Seq Scan on table1 c (cost=0.00..89187.53 > rows=313255 width=367) (actual time=0.032..1783.075 rows=287365 loops=1) > > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > >-> Index Scan using table1_leads_deleted_idx on > table1_leads c (cost=0.00..8.27 rows=1 width=280) (actual > time=0.010..0.010 rows=0 loops=1) > > Index Cond: (deleted = 0) > > Filter: ((module)::text = 'Leads'::text) > > Total runtime: 8432.024 ms > > (12 rows) > > > > I set constraint_exclusion to partition. > > > > Why do I need more time with parition? > > it looks like you don't moved your data from base-table to your partitions. > > regards, jan > >
Re: [PERFORM] Why do I need more time with partition table?
hi al, On Jul 25, 2012, at 10:40 AM, AI Rumman wrote: > Thanks. I missed to add the trigger. > Now I added it, but still without partition taking less time compared to with > partition query. > > With partition :- > > explain analyze > select * > from table1 as c > inner join table2 as a on c.crmid = a.activityid and deleted = 0 > where module ='Leads' > ; > > QUERY > PLAN > -- > Hash Join (cost=25669.79..86440.88 rows=288058 width=367) (actual > time=4411.734..4411.734 rows=0 loops=1) >Hash Cond: (a.activityid = c.crmid) >-> Seq Scan on table2 a (cost=0.00..18337.34 rows=681434 width=139) > (actual time=0.264..1336.555 rows=681434 loops=1) >-> Hash (cost=13207.07..13207.07 rows=288058 width=228) (actual > time=1457.495..1457.495 rows=287365 loops=1) > Buckets: 1024 Batches: 128 Memory Usage: 226kB > -> Append (cost=0.00..13207.07 rows=288058 width=228) (actual > time=0.014..1000.182 rows=287365 loops=1) >-> Seq Scan on table1 c (cost=0.00..0.00 rows=1 width=367) > (actual time=0.001..0.001 rows=0 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) >-> Seq Scan on table1_leads c (cost=0.00..13207.07 > rows=288057 width=228) (actual time=0.010..490.169 rows=287365 loops=1) > Filter: ((deleted = 0) AND ((module)::text = > 'Leads'::text)) > Total runtime: 4412.534 ms > (11 rows) did you have analyze'd your tables? try if indexing column deleted on table1_leads gives you some more speed. regards, jan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why do I need more time with partition table?
On Wed, Jul 25, 2012 at 1:40 AM, AI Rumman wrote: > Thanks. I missed to add the trigger. > Now I added it, but still without partition taking less time compared to > with partition query. Based on the different times on "Seq Scan on table2", it looks like one query has better caching than the other. Did you try running the queries in alternating order, to average out caching effects? Could you run the "explain (analyze, buffers)" on those to get a better picture of the buffer effects? 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
Re: [PERFORM] transactions start time
Aleksei Arefjev writes: > On 24 July 2012 20:21, Richard Huxton wrote: >> I'm not sure if I'm reading this right, but are there more than 48 million >> BEGINs that took 0s each (presumably rounded down) and then a handful >> taking about 0.8s? I'm wondering exactly where/how the duration was measured. If it was at a client, maybe the apparent delay had something to do with network glitches? It seems suspicious that all the outliers are around 0.8s. It would be useful to look to see if there's any comparable pattern for statements other than BEGIN. As Richard says, a BEGIN by itself ought to take negligible time. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] odd planner again, pg 9.0.8
Hi,
Lacking index hints I have a bit of a problem with a slow select.
select
pic.objectid as pic_objectid
,pu.objectid as pu_objectid
,ppcr.preproc_me as pul_preproc_me
,pp.price_amount as pp_price_amount
,pim.aut_item_container as pim_aut_item_container
,COALESCE((select coalesce(pcg.name_preferred, pcg.name) from
codec_gfx pcg where pcg.objectid = pim.aut_codec_gfx), 'unknown') as
pcg_name
,COALESCE((select pis.name from item_snd pis where pis.objectid =
pim.aut_codec_snd), 'unknown') as pis_name
--,(select pii2.price_arr from item_image pii2 where
pii2.item_common = pic.objectid) as pii_price_arr
,pii.price_arr as pii_price_arr
from
(
select
ppcr.item_common
,pul.preproc_me as preproc_me
,pul.ul_finished_at as ul_finished_at
,pul.to_curator_at as to_curator_at
from
pic_curate ppc
,pic_curate_row ppcr
,uploading pul
where
ppc.user_curator = 2 AND
ppcr.pic_curate = ppc.objectid AND
ppcr.item_common = pul.item_common
) ppcr
,item_common pic
left outer join item_movieclip pim on (pim.item_common = pic.objectid)
left outer join item_soundclip pisc on (pisc.item_common =
pic.objectid)
left outer join item_image pii on (pii.item_common = pic.objectid)
,user pu
,pricing pp
where
pic.objectid = ppcr.item_common AND
pu.objectid = pic.user AND
pp.item_common = ppcr.item_common AND
date_trunc ('sec', current_timestamp) BETWEEN pp.startdate and
pp.stopdate
order by
ppcr.item_common
Item_common is the main table. It has some 10M rows
This query executes with...
Nested Loop (cost=256.16..2770236.40 rows=3028 width=523) (actual
time=0.141..64428.788 rows=919 l
oops=1)
-> Nested Loop (cost=256.16..2753774.01 rows=1066 width=515)
(actual time=0.095..64414.614 rows=919 loops=1)
-> Nested Loop (cost=256.16..2753472.18 rows=1066 width=501)
(actual time=0.089..64411.782 rows=919 loops=1)
-> Merge Join (cost=256.16..2750791.56 rows=1066
width=477) (actual time=0.080..64318.897 rows=919 loops=1)
Merge Cond: (pic.objectid = ppcr.item_common)
-> Merge Left Join (cost=251.72..2733545.74
rows=10970452 width=473) (actual time=0.038..63075.673 rows=10831339
loops=1)
Merge Cond: (pic.objectid = pisc.item_common)
-> Merge Left Join (cost=251.72..2689409.45
rows=10970452 width=457) (actual time=0.031..59173.547 rows=10831339
loops=1)
Merge Cond: (pic.objectid =
pii.item_common)
-> Merge Left Join
(cost=251.72..1844762.76 rows=10970452 width=404) (actual
time=0.022..36763.334 rows=10831339 loops=1)
Merge Cond: (pic.objectid =
pim.item_common)
-> Index Scan using
item_common_pkey on item_common pic (cost=0.00..1764469.78
rows=10970452 width=380) (actual time=0.010..20389.141 rows=10831339
loops=1)
-> Index Scan using
item_movieclip_pkey on item_movieclip pim (cost=0.00..34287.89
rows=1486673 width=28) (actual time=0.007..839.065 rows=1440175 loops=1)
-> Index Scan using item_image_pkey
on item_image pii (cost=0.00..707403.77 rows=8785343 width=57) (actual
time=0.007..14972.056 rows=8701222 loops=1)
-> Index Scan using item_soundclip_pkey on
item_soundclip pisc (cost=0.00..10690.67 rows=481559 width=20) (actual
time=0.007..252.650 rows=478672 loops=1)
-> Materialize (cost=0.00..109.95 rows=1066
width=4) (actual time=0.019..1.792 rows=919 loops=1)
-> Nested Loop (cost=0.00..107.28
rows=1066 width=4) (actual time=0.018..1.429 rows=919 loops=1)
Join Filter: (ppc.objectid =
ppcr.pic_curate)
-> Index Scan using
pic_curate_row_pkey on pic_curate_row ppcr (cost=0.00..58.27 rows=3199
width=8) (actual time=0.010..0.650 rows=919 loops=1)
-> Materialize (cost=0.00..1.03
rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=919)
-> Seq Scan on pic_curate ppc
(cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
Filter: (user_curator = 2)
-> Index Scan using uploading_x2 on uploading pul
(cost=0.00..2.50 rows=1 width=24) (actual time=0.100..0.100 rows=1
loops=919)
Index Cond: (pul.item_common = ppcr.item_common)
-> Index Scan using user_pkey on user pu (cost=0.00..0.27
rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=919)
Index Cond: (pu.o
Re: [PERFORM] odd planner again, pg 9.0.8
Hello
you have too slow merge join
maybe you have bloated item_common_pkey or item_common relations -
can you try reindex or vacuum full
you use random_page_cost = 1.0 - it can be source of bad plan
Regards
Pavel Stehule
2012/7/25 Marcus Engene :
> Hi,
>
> Lacking index hints I have a bit of a problem with a slow select.
>
> select
> pic.objectid as pic_objectid
> ,pu.objectid as pu_objectid
> ,ppcr.preproc_me as pul_preproc_me
> ,pp.price_amount as pp_price_amount
> ,pim.aut_item_container as pim_aut_item_container
> ,COALESCE((select coalesce(pcg.name_preferred, pcg.name) from codec_gfx
> pcg where pcg.objectid = pim.aut_codec_gfx), 'unknown') as pcg_name
> ,COALESCE((select pis.name from item_snd pis where pis.objectid =
> pim.aut_codec_snd), 'unknown') as pis_name
> --,(select pii2.price_arr from item_image pii2 where
> pii2.item_common = pic.objectid) as pii_price_arr
> ,pii.price_arr as pii_price_arr
> from
> (
> select
> ppcr.item_common
> ,pul.preproc_me as preproc_me
> ,pul.ul_finished_at as ul_finished_at
> ,pul.to_curator_at as to_curator_at
> from
> pic_curate ppc
> ,pic_curate_row ppcr
> ,uploading pul
> where
> ppc.user_curator = 2 AND
> ppcr.pic_curate = ppc.objectid AND
> ppcr.item_common = pul.item_common
> ) ppcr
> ,item_common pic
> left outer join item_movieclip pim on (pim.item_common = pic.objectid)
> left outer join item_soundclip pisc on (pisc.item_common =
> pic.objectid)
> left outer join item_image pii on (pii.item_common = pic.objectid)
> ,user pu
> ,pricing pp
> where
> pic.objectid = ppcr.item_common AND
> pu.objectid = pic.user AND
> pp.item_common = ppcr.item_common AND
> date_trunc ('sec', current_timestamp) BETWEEN pp.startdate and
> pp.stopdate
> order by
> ppcr.item_common
>
> Item_common is the main table. It has some 10M rows
>
> This query executes with...
>
> Nested Loop (cost=256.16..2770236.40 rows=3028 width=523) (actual
> time=0.141..64428.788 rows=919 l
> oops=1)
>-> Nested Loop (cost=256.16..2753774.01 rows=1066 width=515) (actual
> time=0.095..64414.614 rows=919 loops=1)
> -> Nested Loop (cost=256.16..2753472.18 rows=1066 width=501)
> (actual time=0.089..64411.782 rows=919 loops=1)
>-> Merge Join (cost=256.16..2750791.56 rows=1066 width=477)
> (actual time=0.080..64318.897 rows=919 loops=1)
> Merge Cond: (pic.objectid = ppcr.item_common)
> -> Merge Left Join (cost=251.72..2733545.74
> rows=10970452 width=473) (actual time=0.038..63075.673 rows=10831339
> loops=1)
>Merge Cond: (pic.objectid = pisc.item_common)
>-> Merge Left Join (cost=251.72..2689409.45
> rows=10970452 width=457) (actual time=0.031..59173.547 rows=10831339
> loops=1)
> Merge Cond: (pic.objectid =
> pii.item_common)
> -> Merge Left Join
> (cost=251.72..1844762.76 rows=10970452 width=404) (actual
> time=0.022..36763.334 rows=10831339 loops=1)
>Merge Cond: (pic.objectid =
> pim.item_common)
>-> Index Scan using item_common_pkey
> on item_common pic (cost=0.00..1764469.78 rows=10970452 width=380) (actual
> time=0.010..20389.141 rows=10831339 loops=1)
>-> Index Scan using
> item_movieclip_pkey on item_movieclip pim (cost=0.00..34287.89 rows=1486673
> width=28) (actual time=0.007..839.065 rows=1440175 loops=1)
> -> Index Scan using item_image_pkey on
> item_image pii (cost=0.00..707403.77 rows=8785343 width=57) (actual
> time=0.007..14972.056 rows=8701222 loops=1)
>-> Index Scan using item_soundclip_pkey on
> item_soundclip pisc (cost=0.00..10690.67 rows=481559 width=20) (actual
> time=0.007..252.650 rows=478672 loops=1)
> -> Materialize (cost=0.00..109.95 rows=1066 width=4)
> (actual time=0.019..1.792 rows=919 loops=1)
>-> Nested Loop (cost=0.00..107.28 rows=1066
> width=4) (actual time=0.018..1.429 rows=919 loops=1)
> Join Filter: (ppc.objectid =
> ppcr.pic_curate)
> -> Index Scan using pic_curate_row_pkey on
> pic_curate_row ppcr (cost=0.00..58.27 rows=3199 width=8) (actual
> time=0.010..0.650 rows=919 loops=1)
> -> Materialize (cost=0.00..1.03 rows=1
> width=4) (actual time=0.000..0.000 rows=1 loops=919)
>-> Seq Scan on pic_curate ppc
> (cost=0.00..1.02 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
>
Re: [PERFORM] odd planner again, pg 9.0.8
Marcus Engene writes: > Lacking index hints I have a bit of a problem with a slow select. I don't think you need index hints. What you probably do need is to increase join_collapse_limit and/or from_collapse_limit to deal with this complex query as a whole. > There are several selects looking similar to this in our application > that suddenly jumped from a handfull of ms to many seconds. Perhaps you had those settings adjusted properly and somebody turned them off again? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] transactions start time
Hi, On Wednesday, July 25, 2012 04:56:20 PM Tom Lane wrote: > Aleksei Arefjev writes: > > On 24 July 2012 20:21, Richard Huxton wrote: > >> I'm not sure if I'm reading this right, but are there more than 48 > >> million BEGINs that took 0s each (presumably rounded down) and then a > >> handful taking about 0.8s? > > I'm wondering exactly where/how the duration was measured. If it was at > a client, maybe the apparent delay had something to do with network > glitches? It seems suspicious that all the outliers are around 0.8s. > It would be useful to look to see if there's any comparable pattern > for statements other than BEGIN. > > As Richard says, a BEGIN by itself ought to take negligible time. He earlier also asked on the IRC-Channel and I got the idea that the problem could be explained by pgbouncer in transaction pooling mode waiting for a free backend connection. Aleksei confirmed that they use pgbouncer in that configuration, so that might be it. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] odd planner again, pg 9.0.8
On 7/25/12 6:39 PM, Tom Lane wrote:
Marcus Engene writes:
Lacking index hints I have a bit of a problem with a slow select.
I don't think you need index hints. What you probably do need is to
increase join_collapse_limit and/or from_collapse_limit to deal with
this complex query as a whole.
There are several selects looking similar to this in our application
that suddenly jumped from a handfull of ms to many seconds.
Perhaps you had those settings adjusted properly and somebody turned
them off again?
regards, tom lane
Wonderful mr Lane, now the query executes amazingly fast! I increased
from_collapse_limit from it default 8 to 10 and it behaves as expected.
Thank you!
Marcus
Sort (cost=10628.68..10631.95 rows=1307 width=89) (actual
time=26.430..26.493 rows=919 loops=1)
Sort Key: ppcr.item_common
Sort Method: quicksort Memory: 154kB
-> Nested Loop (cost=0.00..10561.03 rows=1307 width=89) (actual
time=0.093..25.612 rows=919 loops=1)
-> Nested Loop (cost=0.00..3433.41 rows=460 width=85)
(actual time=0.061..13.257 rows=919 loops=1)
-> Nested Loop Left Join (cost=0.00..3134.45 rows=460
width=85) (actual time=0.057..10.972 rows=919 loops=1)
-> Nested Loop Left Join (cost=0.00..2706.99
rows=460 width=32) (actual time=0.053..9.092 rows=919 loops=1)
-> Nested Loop (cost=0.00..2391.21
rows=460 width=20) (actual time=0.047..6.964 rows=919 loops=1)
-> Nested Loop (cost=0.00..1212.82
rows=460 width=12) (actual time=0.039..3.756 rows=919 loops=1)
-> Nested Loop
(cost=0.00..36.70 rows=460 width=4) (actual time=0.028..0.436 rows=919
loops=1)
Join Filter: (ppc.objectid
= ppcr.pic_curate)
-> Seq Scan on pic_curate
ppc (cost=0.00..1.02 rows=1 width=4) (actual time=0.006..0.006 rows=1
loops=1)
Filter:
(user_curator = 2)
-> Seq Scan on
pic_curate_row ppcr (cost=0.00..24.19 rows=919 width=8) (actual
time=0.019..0.147 rows=919 loops=1)
-> Index Scan using
uploading_x2 on uploading pul (cost=0.00..2.54 rows=1 width=8) (actual
time=0.003..0.003 rows=1 loops=919)
Index Cond:
(pul.item_common = ppcr.item_common)
-> Index Scan using item_common_pkey
on item_common pic (cost=0.00..2.55 rows=1 width=8) (actual
time=0.003..0.003 rows=1 loops=919)
Index Cond: (pic.objectid =
ppcr.item_common)
-> Index Scan using item_movieclip_pkey on
item_movieclip pim (cost=0.00..0.67 rows=1 width=16) (actual
time=0.002..0.002 rows=0 loops=919)
Index Cond: (pim.item_common =
pic.objectid)
-> Index Scan using item_image_pkey on item_image
pii (cost=0.00..0.92 rows=1 width=57) (actual time=0.002..0.002 rows=0
loops=919)
Index Cond: (pii.item_common = pic.objectid)
-> Index Scan using user_pkey on user pu
(cost=0.00..0.64 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=919)
Index Cond: (pu.objectid = pic.user)
-> Index Scan using pricing_x1 on pricing pp (cost=0.00..3.63
rows=3 width=12) (actual time=0.004..0.004 rows=1 loops=919)
Index Cond: (pp.item_common = ppcr.item_common)
Filter: ((date_trunc('sec'::text, now()) >=
pp.startdate) AND (date_trunc('sec'::text, now()) <= pp.stopdate))
SubPlan 1
-> Index Scan using codec_gfx_pkey on codec_gfx pcg
(cost=0.00..2.26 rows=1 width=27) (actual time=0.000..0.000 rows=0
loops=919)
Index Cond: (objectid = $0)
SubPlan 2
-> Seq Scan on item_snd pis (cost=0.00..1.90 rows=1
width=15) (actual time=0.007..0.008 rows=0 loops=919)
Filter: (objectid = $1)
Total runtime: 26.795 ms
(34 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda wrote: > This may be another issue of the problem discussed here: > http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html > (Kris Jurka explains the crux of it in that thread). > > Note that it seems the preparing/planning interaction was not the > poster's actual problem, but it may have been yours. As Tom Lane notes > in that thread, this should get better in 9.2. jdbc should get some blame too -- it's really aggressive about preparing queries. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure wrote: > On Tue, Jul 24, 2012 at 10:50 AM, Maciek Sakrejda > wrote: >> This may be another issue of the problem discussed here: >> http://postgresql.1045698.n5.nabble.com/avoid-prepared-statements-on-complex-queries-td4996363.html >> (Kris Jurka explains the crux of it in that thread). >> >> Note that it seems the preparing/planning interaction was not the >> poster's actual problem, but it may have been yours. As Tom Lane notes >> in that thread, this should get better in 9.2. > > jdbc should get some blame too -- it's really aggressive about > preparing queries. > indeed! Is there any reason for that? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao wrote: > On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure wrote: >>> Note that it seems the preparing/planning interaction was not the >>> poster's actual problem, but it may have been yours. As Tom Lane notes >>> in that thread, this should get better in 9.2. >> >> jdbc should get some blame too -- it's really aggressive about >> preparing queries. >> > > indeed! > Is there any reason for that? IMNSHO it's an oversight in the core JDBC design dating back to the beginning: you have two basic choices for executing SQL. The unparameterized Statement or the parameterized PreparedStatement. There should have been a 'ParamaterizedStatement' that gave the expectation of paramaterization without setting up and permanent server side structures to handle the query; libpq makes this distinction and it works very well. Of course, there are various ways to work around this but the point stands. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure wrote: > On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao > wrote: >> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure wrote: Note that it seems the preparing/planning interaction was not the poster's actual problem, but it may have been yours. As Tom Lane notes in that thread, this should get better in 9.2. >>> >>> jdbc should get some blame too -- it's really aggressive about >>> preparing queries. >>> >> >> indeed! >> Is there any reason for that? > > IMNSHO it's an oversight in the core JDBC design dating back to the > beginning: you have two basic choices for executing SQL. The > unparameterized Statement or the parameterized PreparedStatement. > There should have been a 'ParamaterizedStatement' that gave the > expectation of paramaterization without setting up and permanent > server side structures to handle the query; libpq makes this > distinction and it works very well. Of course, there are various ways > to work around this but the point stands. > That is true, I was observing the same, days ago: Running queries and statments in jdbc: https://github.com/vinnix/JavaLab/blob/master/Scrollable.java And running queries with libpq: https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c Is this possible to change something (I really don't know what or where) in the jdbc driver to get more direct aproach? (if that's make any sense to you guys...) Best regards, vinnix -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Wed, Jul 25, 2012 at 2:59 PM, Vinicius Abrahao wrote: > On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure wrote: >> On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao >> wrote: >>> On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure wrote: > Note that it seems the preparing/planning interaction was not the > poster's actual problem, but it may have been yours. As Tom Lane notes > in that thread, this should get better in 9.2. jdbc should get some blame too -- it's really aggressive about preparing queries. >>> >>> indeed! >>> Is there any reason for that? >> >> IMNSHO it's an oversight in the core JDBC design dating back to the >> beginning: you have two basic choices for executing SQL. The >> unparameterized Statement or the parameterized PreparedStatement. >> There should have been a 'ParamaterizedStatement' that gave the >> expectation of paramaterization without setting up and permanent >> server side structures to handle the query; libpq makes this >> distinction and it works very well. Of course, there are various ways >> to work around this but the point stands. >> > > That is true, I was observing the same, days ago: > > Running queries and statments in jdbc: > https://github.com/vinnix/JavaLab/blob/master/Scrollable.java > > And running queries with libpq: > https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c > > Is this possible to change something (I really don't know what or > where) in the jdbc driver > to get more direct aproach? (if that's make any sense to you guys...) you can disable server-side preparing in the url or as library setting. see here: "jdbc:postgresql://localhost:5432/test?prepareThreshold=3"; unfortunately postgres jdbc is bugged and does not honor the above for transaction control commands (begin, commit, etc). This patch http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch will fix it, assuming it hasn't been fixed in recent postgres jdbc. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
Why not just use simple Statement instead of PreparedStatement and construct the SQL with concated string or StringBuilder? like this: int col1=xxx; String col2=""; String sql="select * from table where col1="+col+" and col2='"+col2+"'"; δΊ 2012/7/26 3:59, Vinicius Abrahao ει: On Wed, Jul 25, 2012 at 4:26 PM, Merlin Moncure wrote: On Wed, Jul 25, 2012 at 2:17 PM, Vinicius Abrahao wrote: On Wed, Jul 25, 2012 at 3:45 PM, Merlin Moncure wrote: Note that it seems the preparing/planning interaction was not the poster's actual problem, but it may have been yours. As Tom Lane notes in that thread, this should get better in 9.2. jdbc should get some blame too -- it's really aggressive about preparing queries. indeed! Is there any reason for that? IMNSHO it's an oversight in the core JDBC design dating back to the beginning: you have two basic choices for executing SQL. The unparameterized Statement or the parameterized PreparedStatement. There should have been a 'ParamaterizedStatement' that gave the expectation of paramaterization without setting up and permanent server side structures to handle the query; libpq makes this distinction and it works very well. Of course, there are various ways to work around this but the point stands. That is true, I was observing the same, days ago: Running queries and statments in jdbc: https://github.com/vinnix/JavaLab/blob/master/Scrollable.java And running queries with libpq: https://github.com/vinnix/testLibPQ/blob/master/testlibpq.c Is this possible to change something (I really don't know what or where) in the jdbc driver to get more direct aproach? (if that's make any sense to you guys...) Best regards, vinnix -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
> unfortunately postgres jdbc is bugged and does not honor the above for > transaction control commands (begin, commit, etc). This patch > http://treehou.se/~omar/postgresql-jdbc-8.4-701-pgbouncer_txn.patch > will fix it, assuming it hasn't been fixed in recent postgres jdbc. Looks like it's still an issue: https://github.com/pgjdbc/pgjdbc/blob/master/org/postgresql/core/v3/QueryExecutorImpl.java#L426 Although I don't quite follow why it's an issue in the first place--isn't the point to avoid creating a plan with parameter markers but not actual parameter information? BEGIN, COMMIT, et al never have markers in the first place. What am I missing? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Geoserver-PostGIS performance problems
On Wed, Jul 25, 2012 at 7:13 PM, Rural Hunter wrote: > Why not just use simple Statement instead of PreparedStatement and construct > the SQL with concated string or StringBuilder? like this: > int col1=xxx; > String col2=""; > String sql="select * from table where col1="+col+" and col2='"+col2+"'"; Ah, finally get to apply the old there's-an-xkcd-for-that rule here: http://xkcd.com/327/ Or, more informatively: http://en.wikipedia.org/wiki/SQL_injection Note that it's not completely crazy (in fact, the JDBC driver used to this this forever ago): if you know what you're doing, you *can* safely escape strings and avoid injection. But it's not for the faint of heart. Also, if you control the parameters and can verify that escaping is not (and will never be) necessary over the domain of their possible values, that's another option. But in general, it's safer to let drivers worry about this. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
