pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Avoiding tuple construction/deconstruction during joining
6+ messages / 3 participants
[nested] [flat]

* Avoiding tuple construction/deconstruction during joining
@ 2005-03-14 17:24 Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-14 19:11 ` Re: Avoiding tuple construction/deconstruction during joining Miroslav Šulc <miroslav.sulc@startnet.cz>
  2005-03-15 17:17 ` Re: [PERFORM] Avoiding tuple construction/deconstruction during joining PFC <lists@boutiquenumerique.com>
  2005-03-15 19:53 ` Re: [PERFORM] Avoiding tuple construction/deconstruction during joining PFC <lists@boutiquenumerique.com>
  0 siblings, 3 replies; 6+ messages in thread

From: Tom Lane @ 2005-03-14 17:24 UTC (permalink / raw)
  To: pgsql-hackers@postgreSQL.org; +Cc: Miroslav Šulc <miroslav.sulc@startnet.cz>; pgsql-performance

=?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz> writes:
> [ concerning a deeply nested LEFT JOIN to get data from a star schema ]

> So I have some results. I have tested the query on both PostgreSQL 8.0.1 
> and MySQL 4.1.8 with LIMIT set to 30 and OFFSET set to 6000. PostgreSQL 
> result is 11,667.916 ms, MySQL result is 448.4 ms.

That's a fairly impressive discrepancy :-(, and even the slot_getattr()
patch that Atsushi Ogawa provided isn't going to close the gap.
(I got about a 4x speedup on Miroslav's example in my testing, which
leaves us still maybe 6x slower than MySQL.)

Looking at the post-patch profile for the test case, there is still
quite a lot of cycles going into tuple assembly and disassembly:

Each sample counts as 0.01 seconds.
  %   cumulative   self              self     total           
 time   seconds   seconds    calls  ms/call  ms/call  name    
 24.47      4.49     4.49                             _mcount
  8.01      5.96     1.47  9143692     0.00     0.00  ExecEvalVar
  6.92      7.23     1.27  6614373     0.00     0.00  slot_deformtuple
  6.54      8.43     1.20  9143692     0.00     0.00  slot_getattr
  6.21      9.57     1.14   103737     0.01     0.03  ExecTargetList
  5.56     10.59     1.02   103775     0.01     0.01  DataFill
  3.22     11.18     0.59   103775     0.01     0.01  ComputeDataSize
  2.83     11.70     0.52                             ExecEvalVar
  2.72     12.20     0.50  9094122     0.00     0.00  memcpy
  2.51     12.66     0.46                             encore
  2.40     13.10     0.44   427448     0.00     0.00  nocachegetattr
  2.13     13.49     0.39   103775     0.00     0.02  heap_formtuple
  2.07     13.87     0.38                             noshlibs
  1.20     14.09     0.22   225329     0.00     0.00  _doprnt
  1.20     14.31     0.22                             msquadloop
  1.14     14.52     0.21                             chunks
  0.98     14.70     0.18   871885     0.00     0.00  AllocSetAlloc
  0.98     14.88     0.18                             $$dyncall
  0.76     15.02     0.14   594242     0.00     0.00  FunctionCall3
  0.71     15.15     0.13   213312     0.00     0.00  comparetup_heap
  0.65     15.27     0.12     6364     0.02     0.13  printtup
  0.60     15.38     0.11   790702     0.00     0.00  pfree

(_mcount is profiling overhead, ignore it.)  It looks to me like just
about everything in the top dozen functions is there as a result of the
fact that join steps form new tuples that are the merge of their input
tuples.  Even our favorite villains, palloc and pfree, are down in the
sub-percent range.

I am guessing that the reason MySQL wins on this is that they avoid
doing any data copying during a join step.  I wonder whether we could
accomplish the same by taking Ogawa's patch to the next level: allow
a TupleTableSlot to contain either a "materialized" tuple as now,
or a "virtual" tuple that is simply an array of Datums and null flags.
(It's virtual in the sense that any pass-by-reference Datums would have
to be pointing to data at the next level down.)  This would essentially
turn the formtuple and deformtuple operations into no-ops, and get rid
of a lot of the associated overhead such as ComputeDataSize and
DataFill.  The only operations that would have to forcibly materialize
a tuple would be ones that need to keep the tuple till after they fetch
their next input tuple --- hashing and sorting are examples, but very
many plan node types don't ever need to do that.

I haven't worked out the details, but it seems likely that this could be
a relatively nonintrusive patch.  The main thing that would be an issue
would be that direct reference to slot->val would become verboten (since
you could no longer be sure there was a materialized tuple there).
I think this would possibly affect some contrib stuff, which is a strong
hint that it'd break some existing user-written code out there.

Thoughts?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Avoiding tuple construction/deconstruction during joining
  2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-14 19:11 ` Miroslav Šulc <miroslav.sulc@startnet.cz>
  2005-03-14 19:22   ` Re: Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 6+ messages in thread

From: Miroslav Šulc @ 2005-03-14 19:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

Tom Lane wrote:

>>So I have some results. I have tested the query on both PostgreSQL 8.0.1 
>>and MySQL 4.1.8 with LIMIT set to 30 and OFFSET set to 6000. PostgreSQL 
>>result is 11,667.916 ms, MySQL result is 448.4 ms.
>>    
>>
>
>That's a fairly impressive discrepancy :-(, and even the slot_getattr()
>patch that Atsushi Ogawa provided isn't going to close the gap.
>(I got about a 4x speedup on Miroslav's example in my testing, which
>leaves us still maybe 6x slower than MySQL.)
>  
>
As I wrote, the comparison is not "fair". Here are the conditions:

"Both databases are running on the same machine (my laptop) and contain 
the same data. However there are some differences in the data table 
definitions:
1) in PostgreSQL I use 'varchar(1)' for a lot of fields and in MySQL I 
use 'enum'
2) in PostgreSQL in some cases I use connection fields that are not of 
the same type (smallint <-> integer (SERIAL)), in MySQL I use the same 
types"

For those not used to MySQL, enum is an integer "mapped" to a text 
string (that's how I see it). That means that when you have field such 
as enum('yes','no','DK'), in the table there are stored numbers 1, 2 and 
3 which are mapped to the text values 'yes', 'no' and 'DK'. The 
description is not accurate (I'm not MySQL programmer, I didn't check it 
recently and I didn't inspect the code - I wouldn't understand it 
either) but I think it's not that important. What is important is the 
fact that MySQL has to work with some dozen fields that are numbers and 
PostgreSQL has to work with the same fields as varchar(). Some of the 
other fields are also varchars. This might (or might not) cause the 
speed difference. However I think that if devs figure out how to speed 
this up, other cases will benefit from the improvement too.

As I understood from the contributions of other, the 2) shouldn't have a 
great impact on the speed.

>Looking at the post-patch profile for the test case, there is still
>quite a lot of cycles going into tuple assembly and disassembly:
>
>Each sample counts as 0.01 seconds.
>  %   cumulative   self              self     total           
> time   seconds   seconds    calls  ms/call  ms/call  name    
> 24.47      4.49     4.49                             _mcount
>  8.01      5.96     1.47  9143692     0.00     0.00  ExecEvalVar
>  6.92      7.23     1.27  6614373     0.00     0.00  slot_deformtuple
>  6.54      8.43     1.20  9143692     0.00     0.00  slot_getattr
>  6.21      9.57     1.14   103737     0.01     0.03  ExecTargetList
>  5.56     10.59     1.02   103775     0.01     0.01  DataFill
>  3.22     11.18     0.59   103775     0.01     0.01  ComputeDataSize
>  2.83     11.70     0.52                             ExecEvalVar
>  2.72     12.20     0.50  9094122     0.00     0.00  memcpy
>  2.51     12.66     0.46                             encore
>  2.40     13.10     0.44   427448     0.00     0.00  nocachegetattr
>  2.13     13.49     0.39   103775     0.00     0.02  heap_formtuple
>  2.07     13.87     0.38                             noshlibs
>  1.20     14.09     0.22   225329     0.00     0.00  _doprnt
>  1.20     14.31     0.22                             msquadloop
>  1.14     14.52     0.21                             chunks
>  0.98     14.70     0.18   871885     0.00     0.00  AllocSetAlloc
>  0.98     14.88     0.18                             $$dyncall
>  0.76     15.02     0.14   594242     0.00     0.00  FunctionCall3
>  0.71     15.15     0.13   213312     0.00     0.00  comparetup_heap
>  0.65     15.27     0.12     6364     0.02     0.13  printtup
>  0.60     15.38     0.11   790702     0.00     0.00  pfree
>
>(_mcount is profiling overhead, ignore it.)  It looks to me like just
>about everything in the top dozen functions is there as a result of the
>fact that join steps form new tuples that are the merge of their input
>tuples.  Even our favorite villains, palloc and pfree, are down in the
>sub-percent range.
>
>I am guessing that the reason MySQL wins on this is that they avoid
>doing any data copying during a join step.  I wonder whether we could
>accomplish the same by taking Ogawa's patch to the next level: allow
>a TupleTableSlot to contain either a "materialized" tuple as now,
>or a "virtual" tuple that is simply an array of Datums and null flags.
>(It's virtual in the sense that any pass-by-reference Datums would have
>to be pointing to data at the next level down.)  This would essentially
>turn the formtuple and deformtuple operations into no-ops, and get rid
>of a lot of the associated overhead such as ComputeDataSize and
>DataFill.  The only operations that would have to forcibly materialize
>a tuple would be ones that need to keep the tuple till after they fetch
>their next input tuple --- hashing and sorting are examples, but very
>many plan node types don't ever need to do that.
>
>I haven't worked out the details, but it seems likely that this could be
>a relatively nonintrusive patch.  The main thing that would be an issue
>would be that direct reference to slot->val would become verboten (since
>you could no longer be sure there was a materialized tuple there).
>I think this would possibly affect some contrib stuff, which is a strong
>hint that it'd break some existing user-written code out there.
>
>Thoughts?
>  
>
Mine thought is that I don't know what you are talking about :-) Now 
seriously, I am far below this level of knowledge. But I can contribute 
a test that (maybe) can help. I have rewritten the query so it JOINs the 
varchar() fields (in fact all fields except the IDPK) at the last INNER 
JOIN. Though there is one more JOIN, the query is more than 5 times 
faster (1975.312 ms) :-)

So my silly opinion is that if the planner could decide that there are 
too much time expensive fields that are not needed during performing 
JOINs and these could be JOINed at the last step, it would do it this 
way :-)

Below is the adjusted query and the EXPLAIN ANALYZE output. (Tom, you 
can run it on the data I have sent you and it should run without changes.)

SELECT AdDevicesSites.IDPK, AdDevicesSites2.AdDevicesSiteSizeIDFK, 
AdDevicesSites2.AdDevicesSiteRegionIDFK, 
AdDevicesSites2.AdDevicesSiteCountyIDFK, 
AdDevicesSites2.AdDevicesSiteCityIDFK, 
AdDevicesSites2.AdDevicesSiteDistrictIDFK, 
AdDevicesSites2.AdDevicesSiteStreetIDFK, 
AdDevicesSites2.AdDevicesSiteStreetDescriptionIDFK, 
AdDevicesSites2.AdDevicesSitePositionIDFK, 
AdDevicesSites2.AdDevicesSiteVisibilityIDFK, 
AdDevicesSites2.AdDevicesSiteStatusTypeIDFK, 
AdDevicesSites2.AdDevicesSitePartnerIdentificationOperatorIDFK, 
AdDevicesSites2.AdDevicesSitePartnerElectricitySupplierIDFK, 
AdDevicesSites2.AdDevicesSitePartnerMaintainerIDFK, 
AdDevicesSites2.AdDevicesSitePartnerStickerIDFK, 
AdDevicesSites2.CadastralUnitIDFK, AdDevicesSites2.MediaType, 
AdDevicesSites2.Mark, AdDevicesSites2.Amount, AdDevicesSites2.Distance, 
AdDevicesSites2.OwnLightening, AdDevicesSites2.LocationDownTown, 
AdDevicesSites2.LocationSuburb, 
AdDevicesSites2.LocationBusinessDistrict, 
AdDevicesSites2.LocationResidentialDistrict, 
AdDevicesSites2.LocationIndustrialDistrict, 
AdDevicesSites2.LocationNoBuildings, AdDevicesSites2.ParkWayHighWay, 
AdDevicesSites2.ParkWayFirstClassRoad, AdDevicesSites2.ParkWayOtherRoad, 
AdDevicesSites2.ParkWayStreet, AdDevicesSites2.ParkWayAccess, 
AdDevicesSites2.ParkWayExit, AdDevicesSites2.ParkWayParkingPlace, 
AdDevicesSites2.ParkWayPassangersOnly, AdDevicesSites2.ParkWayCrossRoad, 
AdDevicesSites2.PositionStandAlone, 
AdDevicesSites2.NeighbourhoodPublicTransportation, 
AdDevicesSites2.NeighbourhoodInterCityTransportation, 
AdDevicesSites2.NeighbourhoodPostOffice, 
AdDevicesSites2.NeighbourhoodNewsStand, 
AdDevicesSites2.NeighbourhoodAmenities, 
AdDevicesSites2.NeighbourhoodSportsSpot, 
AdDevicesSites2.NeighbourhoodHealthServiceSpot, 
AdDevicesSites2.NeighbourhoodShops, 
AdDevicesSites2.NeighbourhoodShoppingCenter, 
AdDevicesSites2.NeighbourhoodSuperMarket, 
AdDevicesSites2.NeighbourhoodPetrolStation, 
AdDevicesSites2.NeighbourhoodSchool, AdDevicesSites2.NeighbourhoodBank, 
AdDevicesSites2.NeighbourhoodRestaurant, 
AdDevicesSites2.NeighbourhoodHotel, 
AdDevicesSites2.RestrictionCigarettes, 
AdDevicesSites2.RestrictionPolitics, AdDevicesSites2.RestrictionSpirits, 
AdDevicesSites2.RestrictionSex, AdDevicesSites2.RestrictionOther, 
AdDevicesSites2.RestrictionNote, AdDevicesSites2.SpotMapFile, 
AdDevicesSites2.SpotPhotoFile, AdDevicesSites2.SourcePhotoTimeStamp, 
AdDevicesSites2.SourceMapTimeStamp, AdDevicesSites2.Price, 
AdDevicesSites2.WebPrice, AdDevicesSites2.CadastralUnitCode, 
AdDevicesSites2.BuildingNumber, AdDevicesSites2.ParcelNumber, 
AdDevicesSites2.GPSLatitude, AdDevicesSites2.GPSLongitude, 
AdDevicesSites2.GPSHeight, AdDevicesSites2.MechanicalOpticalCoordinates, 
AdDevicesSites2.Deleted, AdDevicesSites2.Protected, 
AdDevicesSites2.DateCreated, AdDevicesSites2.DateLastModified, 
AdDevicesSites2.DateDeleted, AdDevicesSites2.CreatedByUserIDFK, 
AdDevicesSites2.LastModifiedByUserIDFK, 
AdDevicesSites2.DeletedByUserIDFK, 
AdDevicesSites2.PhotoLastModificationDate, 
AdDevicesSites2.MapLastModificationDate, 
AdDevicesSites2.DateLastImported, AdDevicesSiteRegions.Name AS 
AdDevicesSiteRegionName, AdDevicesSiteCounties.Name AS 
AdDevicesSiteCountyName, AdDevicesSiteCities.Name AS 
AdDevicesSiteCityName, AdDevicesSiteStreets.Name AS 
AdDevicesSiteStreetName, AdDevicesSiteDistricts.Name AS 
AdDevicesSiteDistrictName, AdDevicesSiteStreetDescriptions.Name_cs AS 
AdDevicesSiteStreetDescriptionName_cs, 
AdDevicesSiteStreetDescriptions.Name_en AS 
AdDevicesSiteStreetDescriptionName_en, AdDevicesSiteSizes.Name AS 
AdDevicesSiteSizeName, SUBSTRING(AdDevicesSiteVisibilities.Name_cs, 3) 
AS AdDevicesSiteVisibilityName_cs, 
SUBSTRING(AdDevicesSiteVisibilities.Name_en, 3) AS 
AdDevicesSiteVisibilityName_en, AdDevicesSitePositions.Name_cs AS 
AdDevicesSitePositionName_cs, AdDevicesSitePositions.Name_en AS 
AdDevicesSitePositionName_en, AdDevicesSiteStatusTypes.Name_cs AS 
AdDevicesSiteStatusTypeName_cs, AdDevicesSiteStatusTypes.Name_en AS 
AdDevicesSiteStatusTypeName_en, PartnerIdentificationsOperator.Name AS 
PartnerIdentificationOperatorName, PartnersElectricitySupplier.Name AS 
PartnerElectricitySupplierName, PartnersMaintainer.Name AS 
PartnerMaintainerName, PartnersSticker.Name AS PartnerStickerName, 
CadastralUnits.Code AS CadastralUnitCodeNative, CadastralUnits.Name AS 
CadastralUnitName
FROM AdDevicesSites
LEFT JOIN AdDevicesSiteRegions ON AdDevicesSites.AdDevicesSiteRegionIDFK 
= AdDevicesSiteRegions.IDPK
LEFT JOIN AdDevicesSiteCounties ON 
AdDevicesSites.AdDevicesSiteCountyIDFK = AdDevicesSiteCounties.IDPK
LEFT JOIN AdDevicesSiteCities ON AdDevicesSites.AdDevicesSiteCityIDFK = 
AdDevicesSiteCities.IDPK
LEFT JOIN AdDevicesSiteStreets ON AdDevicesSites.AdDevicesSiteStreetIDFK 
= AdDevicesSiteStreets.IDPK
LEFT JOIN AdDevicesSiteStreetDescriptions ON 
AdDevicesSites.AdDevicesSiteStreetDescriptionIDFK = 
AdDevicesSiteStreetDescriptions.IDPK
LEFT JOIN AdDevicesSiteDistricts ON 
AdDevicesSites.AdDevicesSiteDistrictIDFK = AdDevicesSiteDistricts.IDPK
LEFT JOIN AdDevicesSiteSizes ON AdDevicesSites.AdDevicesSiteSizeIDFK = 
AdDevicesSiteSizes.IDPK
LEFT JOIN AdDevicesSiteVisibilities ON 
AdDevicesSites.AdDevicesSiteVisibilityIDFK = AdDevicesSiteVisibilities.IDPK
LEFT JOIN AdDevicesSitePositions ON 
AdDevicesSites.AdDevicesSitePositionIDFK = AdDevicesSitePositions.IDPK
LEFT JOIN AdDevicesSiteStatusTypes ON 
AdDevicesSites.AdDevicesSiteStatusTypeIDFK = AdDevicesSiteStatusTypes.IDPK
LEFT JOIN PartnerIdentifications AS PartnerIdentificationsOperator ON 
AdDevicesSites.AdDevicesSitePartnerIdentificationOperatorIDFK = 
PartnerIdentificationsOperator.IDPK
LEFT JOIN Partners AS PartnersElectricitySupplier ON 
AdDevicesSites.AdDevicesSitePartnerElectricitySupplierIDFK = 
PartnersElectricitySupplier.IDPK
LEFT JOIN Partners AS PartnersMaintainer ON 
AdDevicesSites.AdDevicesSitePartnerMaintainerIDFK = PartnersMaintainer.IDPK
LEFT JOIN Partners AS PartnersSticker ON 
AdDevicesSites.AdDevicesSitePartnerStickerIDFK = PartnersSticker.IDPK
LEFT JOIN CadastralUnits ON AdDevicesSites.CadastralUnitIDFK = 
CadastralUnits.IDPK
INNER JOIN AdDevicesSites AS AdDevicesSites2 ON AdDevicesSites.IDPK = 
AdDevicesSites2.IDPK

QUERY PLAN

Hash Join  (cost=193867.68..235417.92 rows=142556 width=815) (actual time=1577.080..2200.677 rows=6364 loops=1)

  Hash Cond: ("outer".idpk = "inner".idpk)

  ->  Seq Scan on addevicessites addevicessites2  (cost=0.00..13118.56 rows=142556 width=473) (actual time=40.650..49.195 rows=6364 loops=1)

  ->  Hash  (cost=186898.29..186898.29 rows=142556 width=350) (actual time=1534.080..1534.080 rows=0 loops=1)

        ->  Merge Right Join  (cost=184758.38..186898.29 rows=142556 width=350) (actual time=1187.653..1244.955 rows=6364 loops=1)

              Merge Cond: ("outer".idpk = "inner".cadastralunitidfk)

              ->  Index Scan using cadastralunits_pkey on cadastralunits  (cost=0.00..314.49 rows=13027 width=31) (actual time=0.034..0.128 rows=63 loops=1)

              ->  Sort  (cost=184758.38..185114.77 rows=142556 width=325) (actual time=1187.582..1190.111 rows=6364 loops=1)

                    Sort Key: addevicessites.cadastralunitidfk

                    ->  Hash Left Join  (cost=93887.04..143074.76 rows=142556 width=325) (actual time=502.584..1129.145 rows=6364 loops=1)

                          Hash Cond: ("outer".addevicessitepartnerstickeridfk = "inner".idpk)

                          ->  Hash Left Join  (cost=93884.28..140933.65 rows=142556 width=307) (actual time=502.388..1075.572 rows=6364 loops=1)

                                Hash Cond: ("outer".addevicessitepartnermaintaineridfk = "inner".idpk)

                                ->  Hash Left Join  (cost=93881.51..138792.55 rows=142556 width=289) (actual time=502.208..1023.802 rows=6364 loops=1)

                                      Hash Cond: ("outer".addevicessitepartnerelectricitysupplieridfk = "inner".idpk)

                                      ->  Hash Left Join  (cost=93878.75..136651.45 rows=142556 width=271) (actual time=502.041..969.959 rows=6364 loops=1)

                                            Hash Cond: ("outer".addevicessitepartneridentificationoperatoridfk = "inner".idpk)

                                            ->  Nested Loop Left Join  (cost=93875.99..134510.35 rows=142556 width=253) (actual time=501.849..915.256 rows=6364 loops=1)

                                                  Join Filter: ("outer".addevicessitestatustypeidfk = "inner".idpk)

                                                  ->  Nested Loop Left Join  (cost=93874.93..118471.74 rows=142556 width=228) (actual time=501.826..818.436 rows=6364 loops=1)

                                                        Join Filter: ("outer".addevicessitepositionidfk = "inner".idpk)

                                                        ->  Nested Loop Left Join  (cost=93873.90..108848.18 rows=142556 width=207) (actual time=501.802..737.137 rows=6364 loops=1)

                                                              Join Filter: ("outer".addevicessitevisibilityidfk = "inner".idpk)

                                                              ->  Merge Right Join  (cost=93872.86..96017.09 rows=142556 width=177) (actual time=501.741..554.834 rows=6364 loops=1)

                                                                    Merge Cond: ("outer".idpk = "inner".addevicessitesizeidfk)

                                                                    ->  Index Scan using addevicessitesizes_pkey on addevicessitesizes  (cost=0.00..5.62 rows=110 width=14) (actual time=0.009..0.264 rows=110 loops=1)

                                                                    ->  Sort  (cost=93872.86..94229.25 rows=142556 width=169) (actual time=501.697..504.267 rows=6364 loops=1)

                                                                          Sort Key: addevicessites.addevicessitesizeidfk

                                                                          ->  Hash Left Join  (cost=57752.91..65764.23 rows=142556 width=169) (actual time=234.321..466.130 rows=6364 loops=1)

                                                                                Hash Cond: ("outer".addevicessitedistrictidfk = "inner".idpk)

                                                                                ->  Hash Left Join  (cost=57743.17..63616.15 rows=142556 width=164) (actual time=233.456..421.267 rows=6364 loops=1)

                                                                                      Hash Cond: ("outer".addevicessitestreetdescriptionidfk = "inner".idpk)

                                                                                      ->  Hash Left Join  (cost=57634.86..62707.43 rows=142556 width=137) (actual time=223.396..362.945 rows=6364 loops=1)

                                                                                            Hash Cond: ("outer".addevicessitestreetidfk = "inner".idpk)

                                                                                            ->  Hash Left Join  (cost=57566.95..61844.20 rows=142556 width=119) (actual time=217.101..312.605 rows=6364 loops=1)

                                                                                                  Hash Cond: ("outer".addevicessitecityidfk = "inner".idpk)

                                                                                                  ->  Merge Right Join  (cost=57561.85..59700.76 rows=142556 width=110) (actual time=216.635..266.672 rows=6364 loops=1)

                                                                                                        Merge Cond: ("outer".idpk = "inner".addevicessitecountyidfk)

                                                                                                        ->  Sort  (cost=6.19..6.48 rows=117 width=17) (actual time=0.350..0.389 rows=116 loops=1)

                                                                                                              Sort Key: addevicessitecounties.idpk

                                                                                                              ->  Seq Scan on addevicessitecounties  (cost=0.00..2.17 rows=117 width=17) (actual time=0.008..0.146 rows=117 loops=1)

                                                                                                        ->  Sort  (cost=57555.66..57912.05 rows=142556 width=99) (actual time=216.250..218.611 rows=6364 loops=1)

                                                                                                              Sort Key: addevicessites.addevicessitecountyidfk

                                                                                                              ->  Merge Right Join  (cost=33573.63..35712.03 rows=142556 width=99) (actual time=173.646..199.036 rows=6364 loops=1)

                                                                                                                    Merge Cond: ("outer".idpk = "inner".addevicessiteregionidfk)

                                                                                                                    ->  Sort  (cost=1.44..1.48 rows=15 width=23) (actual time=0.055..0.059 rows=13 loops=1)

                                                                                                                          Sort Key: addevicessiteregions.idpk

                                                                                                                          ->  Seq Scan on addevicessiteregions  (cost=0.00..1.15 rows=15 width=23) (actual time=0.016..0.032 rows=15 loops=1)

                                                                                                                    ->  Sort  (cost=33572.19..33928.58 rows=142556 width=82) (actual time=173.559..176.398 rows=6364 loops=1)

                                                                                                                          Sort Key: addevicessites.addevicessiteregionidfk

                                                                                                                          ->  Seq Scan on addevicessites  (cost=0.00..13118.56 rows=142556 width=82) (actual time=62.345..164.783 rows=6364 loops=1)

                                                                                                  ->  Hash  (cost=4.48..4.48 rows=248 width=17) (actual time=0.283..0.283 rows=0 loops=1)

                                                                                                        ->  Seq Scan on addevicessitecities  (cost=0.00..4.48 rows=248 width=17) (actual time=0.011..0.162 rows=138 loops=1)

                                                                                            ->  Hash  (cost=60.53..60.53 rows=2953 width=34) (actual time=6.229..6.229 rows=0 loops=1)

                                                                                                  ->  Seq Scan on addevicessitestreets  (cost=0.00..60.53 rows=2953 width=34) (actual time=0.010..3.816 rows=2984 loops=1)

                                                                                      ->  Hash  (cost=96.85..96.85 rows=4585 width=43) (actual time=10.017..10.017 rows=0 loops=1)

                                                                                            ->  Seq Scan on addevicessitestreetdescriptions  (cost=0.00..96.85 rows=4585 width=43) (actual time=0.008..6.371 rows=4585 loops=1)

                                                                                ->  Hash  (cost=8.59..8.59 rows=459 width=21) (actual time=0.815..0.815 rows=0 loops=1)

                                                                                      ->  Seq Scan on addevicessitedistricts  (cost=0.00..8.59 rows=459 width=21) (actual time=0.007..0.541 rows=382 loops=1)

                                                              ->  Materialize  (cost=1.04..1.08 rows=4 width=36) (actual time=0.000..0.002 rows=4 loops=6364)

                                                                    ->  Seq Scan on addevicessitevisibilities  (cost=0.00..1.04 rows=4 width=36) (actual time=0.026..0.035 rows=4 loops=1)

                                                        ->  Materialize  (cost=1.03..1.06 rows=3 width=27) (actual time=0.000..0.001 rows=3 loops=6364)

                                                              ->  Seq Scan on addevicessitepositions  (cost=0.00..1.03 rows=3 width=27) (actual time=0.007..0.010 rows=3 loops=1)

                                                  ->  Materialize  (cost=1.05..1.10 rows=5 width=31) (actual time=0.000..0.002 rows=5 loops=6364)

                                                        ->  Seq Scan on addevicessitestatustypes  (cost=0.00..1.05 rows=5 width=31) (actual time=0.006..0.016 rows=5 loops=1)

                                            ->  Hash  (cost=2.61..2.61 rows=61 width=34) (actual time=0.144..0.144 rows=0 loops=1)

                                                  ->  Seq Scan on partneridentifications partneridentificationsoperator  (cost=0.00..2.61 rows=61 width=34) (actual time=0.007..0.103 rows=61 loops=1)

                                      ->  Hash  (cost=2.61..2.61 rows=61 width=34) (actual time=0.122..0.122 rows=0 loops=1)

                                            ->  Seq Scan on partners partnerselectricitysupplier  (cost=0.00..2.61 rows=61 width=34) (actual time=0.004..0.072 rows=61 loops=1)

                                ->  Hash  (cost=2.61..2.61 rows=61 width=34) (actual time=0.136..0.136 rows=0 loops=1)

                                      ->  Seq Scan on partners partnersmaintainer  (cost=0.00..2.61 rows=61 width=34) (actual time=0.005..0.086 rows=61 loops=1)

                          ->  Hash  (cost=2.61..2.61 rows=61 width=34) (actual time=0.143..0.143 rows=0 loops=1)

                                ->  Seq Scan on partners partnerssticker  (cost=0.00..2.61 rows=61 width=34) (actual time=0.009..0.098 rows=61 loops=1)

Total runtime: 2210.937 ms


>			regards, tom lane
>  
>
Miroslav

Attachments:

miroslav.sulc.vcf (text/x-vcard, 385B) download begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:miroslav.sulc@startnet.cz title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: Avoiding tuple construction/deconstruction during joining
  2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-14 19:11 ` Re: Avoiding tuple construction/deconstruction during joining Miroslav Šulc <miroslav.sulc@startnet.cz>
@ 2005-03-14 19:22   ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-14 19:31     ` Re: Avoiding tuple construction/deconstruction during joining Miroslav Šulc <miroslav.sulc@startnet.cz>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2005-03-14 19:22 UTC (permalink / raw)
  To: Miroslav Šulc <miroslav.sulc@startnet.cz>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

=?windows-1250?Q?Miroslav_=8Aulc?= <miroslav.sulc@startnet.cz> writes:
> seriously, I am far below this level of knowledge. But I can contribute 
> a test that (maybe) can help. I have rewritten the query so it JOINs the 
> varchar() fields (in fact all fields except the IDPK) at the last INNER 
> JOIN. Though there is one more JOIN, the query is more than 5 times 
> faster (1975.312 ms) :-)

That confirms my thought that passing the data up through multiple
levels of join is what's killing us.  I'll work on a solution.  This
will of course be even less back-patchable to 8.0.* than Ogawa's work,
but hopefully it will fix the issue for 8.1.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Avoiding tuple construction/deconstruction during joining
  2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
  2005-03-14 19:11 ` Re: Avoiding tuple construction/deconstruction during joining Miroslav Šulc <miroslav.sulc@startnet.cz>
  2005-03-14 19:22   ` Re: Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-14 19:31     ` Miroslav Šulc <miroslav.sulc@startnet.cz>
  0 siblings, 0 replies; 6+ messages in thread

From: Miroslav Šulc @ 2005-03-14 19:31 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-performance

Tom Lane wrote:

>=?windows-1250?Q?Miroslav_=8Aulc?= <miroslav.sulc@startnet.cz> writes:
>  
>
>>seriously, I am far below this level of knowledge. But I can contribute 
>>a test that (maybe) can help. I have rewritten the query so it JOINs the 
>>varchar() fields (in fact all fields except the IDPK) at the last INNER 
>>JOIN. Though there is one more JOIN, the query is more than 5 times 
>>faster (1975.312 ms) :-)
>>    
>>
>
>That confirms my thought that passing the data up through multiple
>levels of join is what's killing us.  I'll work on a solution.  This
>will of course be even less back-patchable to 8.0.* than Ogawa's work,
>but hopefully it will fix the issue for 8.1.
>
>			regards, tom lane
>  
>
Tom, thank you and the others for help. I'm glad that my problem can 
help PostgreSQL improve and that there are people like you that 
understand each little bit of the software :-)

Miroslav

Attachments:

miroslav.sulc.vcf (text/x-vcard, 385B) download begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:miroslav.sulc@startnet.cz title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: [PERFORM] Avoiding tuple construction/deconstruction during joining
  2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-15 17:17 ` PFC <lists@boutiquenumerique.com>
  2 siblings, 0 replies; 6+ messages in thread

From: PFC @ 2005-03-15 17:17 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; +Cc: Miroslav Šulc <miroslav.sulc@startnet.cz>; pgsql-performance


	I have asked him for the data and played with his queries, and obtained  
massive speedups with the following queries :

http://boutiquenumerique.com/pf/miroslav/query.sql
http://boutiquenumerique.com/pf/miroslav/query2.sql
http://boutiquenumerique.com/pf/miroslav/materialize.sql

	Note that my optimized version of the Big Joins is not much faster that  
the materialized view without index (hash joins are damn fast in postgres)  
but of course using an index...



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] Avoiding tuple construction/deconstruction during joining
  2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-15 19:53 ` PFC <lists@boutiquenumerique.com>
  2 siblings, 0 replies; 6+ messages in thread

From: PFC @ 2005-03-15 19:53 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; +Cc: Miroslav Šulc <miroslav.sulc@startnet.cz>; pgsql-performance


	On my machine (Laptop with Pentium-M 1.6 GHz and 512MB DDR333) I get the  
following timings :

	Big Joins Query will all the fields and no order by (I just put a SELECT  
* in the first table) yielding about 6k rows :
	=> 12136.338 ms

	Replacing the SELECT * from the table with many fields by just a SELECT  
of the foreign key columns :
	=> 1874.612 ms

	I felt like playing a bit so I implemented a hash join in python  
(download the file, it works on Miroslav's data) :
	All timings do not include time to fetch the data from the database.  
Fetching all the tables takes about 1.1 secs.

	* With something that looks like the current implementation (copying  
tuples around) and fetching all the fields from the big table :
	=> Fetching all the tables : 1.1 secs.
	=> Joining : 4.3 secs

	* Fetching only the integer fields
	=> Fetching all the tables : 0.4 secs.
	=> Joining : 1.7 secs

	* A smarter join which copies nothing and updates the rows as they are  
processed, adding fields :
	=> Fetching all the tables :  1.1 secs.
	=> Joining : 0.4 secs
	With the just-in-time compiler activated, it goes down to about 0.25  
seconds.

	First thing, this confirms what Tom said.
	It also means that doing this query in the application can be a lot  
faster than doing it in postgres including fetching all of the tables.  
There's a problem somewhere ! It should be the other way around ! The  
python mappings (dictionaries : { key : value } ) are optimized like crazy  
but they store column names for each row. And it's a dynamic script  
language ! Argh.

	Note : run the program like this :

python test.py |less -S

	So that the time spent scrolling your terminal does not spoil the  
measurements.

Download test program :
http://boutiquenumerique.com/pf/miroslav/test.py



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-03-15 19:53 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-14 17:24 Avoiding tuple construction/deconstruction during joining Tom Lane <tgl@sss.pgh.pa.us>
2005-03-14 19:11 ` Miroslav Šulc <miroslav.sulc@startnet.cz>
2005-03-14 19:22   ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-14 19:31     ` Miroslav Šulc <miroslav.sulc@startnet.cz>
2005-03-15 17:17 ` PFC <lists@boutiquenumerique.com>
2005-03-15 19:53 ` PFC <lists@boutiquenumerique.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