First off, thanks for your quick replies! I will look into this, but I can tell you that the arrays are strictly numbers and the array columns are type numeric[]
Thanks again, Adrian On Jul 19, 2010, at 3:47 PM, Michael Bayer wrote: > > On Jul 19, 2010, at 1:53 PM, Adrian Price-Whelan wrote: > >> Here is some more detailed information trying the query multiple ways: >> >> Piping the command into psql and writing to a tmp file takes 12 seconds (tmp >> file is 241MB): >> >> time echo "SELECT spectrum.pk AS spectrum_pk, spectrum.ra AS spectrum_ra, >> spectrum.dec AS spectrum_dec, spectrum.values AS spectrum_values, >> spectrum.spectrum_header_pk AS spectrum_spectrum_header_pk, >> spectrum.fiber_number AS spectrum_fiber_number, spectrum.inv_var AS >> spectrum_inv_var, spectrum.and_mask AS spectrum_and_mask, spectrum.or_mask >> AS spectrum_or_mask FROM spectrum JOIN spectrum_header ON spectrum_header.pk >> = spectrum.spectrum_header_pk WHERE spectrum_header.filename = >> 'spPlate-3586-55181.fits';" | psql spectradb -U postgres > tmp >> >> real 0m12.052s >> user 0m2.501s >> sys 0m0.274s >> >> engine.execute on the same query took ~6 seconds: >> spectra = engine.execute("SELECT spectrum.pk AS spectrum_pk, spectrum.ra AS >> spectrum_ra, spectrum.dec AS spectrum_dec, spectrum.values AS >> spectrum_values, spectrum.spectrum_header_pk AS spectrum_spectrum_header_pk, >> spectrum.fiber_number AS spectrum_fiber_number, spectrum.inv_var AS >> spectrum_inv_var, spectrum.and_mask AS spectrum_and_mask, spectrum.or_mask >> AS spectrum_or_mask FROM spectrum JOIN spectrum_header ON spectrum_header.pk >> = spectrum.spectrum_header_pk WHERE spectrum_header.filename = >> 'spPlate-3586-55181.fits';") > > Call fetchall() on the result to get a better idea what's going on. Here's > the source of the ARRAY type: > > def result_processor(self, dialect, coltype): > item_proc = self.item_type.result_processor(dialect, coltype) > if item_proc: > def convert_item(item): > if isinstance(item, list): > return [convert_item(child) for child in item] > else: > return item_proc(item) > else: > def convert_item(item): > if isinstance(item, list): > return [convert_item(child) for child in item] > else: > return item > def process(value): > if value is None: > return value > return [convert_item(item) for item in value] > return process > > as you can see, if your ARRAY is of a Unicode type or similar, convert() must > be called on each item (only happens during fetch). This is a likely cause > of the slowdown and you should consider what kind of converters you're using > on your ARRAY members. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.