-
Notifications
You must be signed in to change notification settings - Fork 61
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Advice for properly timing Django queryset evaluation #61
Comments
Hi @rsomani95, Postgres will figure out the best way to execute the query (in most situations), which involves minimizing work. You can use |
@ankane First, here's a simplified version of the code above: # `embeddable_cls` is a Django Model
# `candidate_ids` is a list of ids to limit the scope of the vector filtering
scoped_qset = embeddable_cls.objects.filter(id__in=candidate_ids)
queryset = (
scoped_qset
.annotate(distance=CosineDistance(embedding_name, embedding))
.filter(distance__lt=query_arguments.distance)
)
filtered_ids = list(queryset.values_list("id", flat=True)) In this case, Executed SQL
SELECT "ozu_api_segment"."id"
FROM "ozu_api_segment"
WHERE ("ozu_api_segment"."id" IN ('36d3abe1-2cf5-4796-8445-a7529ff0c8fb'::uuid, 'f1d02d3d-7861-40c6-95ae-627e791983cf'::uuid, '32a6be12-617b-4ef8-b580-698b99628b01'::uuid, '2f7cb18d-7e09-4b6b-976a-b16db475ce2f'::uuid, '5655dd82-fe55-426d-b54e-38663e1f13a2'::uuid, '1cca49ea-e27f-40af-82f3-d37417073ffe'::uuid, '887ad098-be19-4575-ac30-679cb65a150a'::uuid, 'a48b75f2-ca12-4e52-b843-664067bf03e4'::uuid, '4c72595c-fade-4af2-84e1-723b0dc23422'::uuid, '9973b052-27d0-4378-b7ba-0c56027e3c21'::uuid, '93f1b0aa-ea42-4b85-b216-7bc2b2e15a16'::uuid, 'bb6eded5-8855-4766-b892-dcb420710ec3'::uuid, 'ea2d008f-3108-4fe1-b82b-c0c0b4784005'::uuid, '6e2609a4-4e7a-4fb1-865e-9b9d707b08a1'::uuid, 'a176518f-f7cf-4171-9802-9f191caa7d87'::uuid, '02b0ced8-490b-4ef2-9300-bdf702102843'::uuid, '62e8f839-3023-4da8-b531-59a6dd61fd25'::uuid, 'f74db32a-705f-436b-bcf0-da480e535755'::uuid, 'b8cc4c88-5f4c-4408-aea3-6bac56cd4caf'::uuid, '2825b039-2a51-45cc-83e0-51ad0c4a4af9'::uuid, '33c0bc96-57f6-4415-9b4c-38890c53332e'::uuid, 'd7b35172-d824-43ae-8a89-e1974db7d9f2'::uuid, '7b3ae0c2-6526-437a-b10e-491826d197bf'::uuid, '24a5182d-ce1c-43a8-9dc0-02b0f6fc81a0'::uuid, 'fc94f78c-6120-4efc-8512-5b89f8b85298'::uuid, '2d98b2e0-3c5f-4067-9aea-3f00472342c2'::uuid, 'eda81005-1182-40fd-b486-7d1ea9a690b7'::uuid, '62dedc3d-b2f2-414b-b2bc-6c6bf57b7cda'::uuid, '432b3a21-acc8-416f-921e-541a03eb0460'::uuid, '0ea707b5-a54a-4a59-b362-95b0383c77a5'::uuid, 'cbf00545-2ab4-48ac-bcde-3b535e36e45d'::uuid, 'cbf8e005-5197-441a-ba45-5bb450b69707'::uuid, '6ee8ee78-c360-48c2-b6f5-1bf73cddaa9d'::uuid) AND ("ozu_api_segment"."visual_cinema_clip_embedding" <=> '[0.0003764629364013672,-0.020965576171875,0.04302978515625,0.0278472900390625,-0.0251007080078125,-0.003215789794921875,-0.0175018310546875,0.06768798828125,-0.00933074951171875,-0.007747650146484375,-0.034759521484375,-0.022918701171875,0.0253753662109375,0.0235748291015625,0.043487548828125,0.01320648193359375,0.00804901123046875,-0.004238128662109375,-0.0309906005859375,-0.041534423828125,-0.0245208740234375,-0.0198516845703125,-0.031005859375,-0.061187744140625,0.0034580230712890625,0.01380157470703125,0.00882720947265625,0.0016460418701171875,-0.04913330078125,0.08111572265625,0.08233642578125,0.0239105224609375,-0.0213470458984375,0.066162109375,-0.0027675628662109375,-0.0251617431640625,0.0285491943359375,-0.0015277862548828125,0.0206146240234375,0.0693359375,0.026153564453125,-0.0021762847900390625,0.034698486328125,-0.00156402587890625,-0.020599365234375,0.060394287109375,-0.053131103515625,-0.0164794921875,0.03680419921875,-0.0090789794921875,0.033233642578125,0.0079803466796875,-0.01136016845703125,-0.00011813640594482422,0.09735107421875,-0.004077911376953125,-0.036041259765625,0.01226043701171875,0.0308380126953125,0.00339508056640625,-0.03497314453125,0.02703857421875,-0.028045654296875,0.02752685546875,-0.005718231201171875,-0.0013713836669921875,0.004856109619140625,-0.01421356201171875,-0.0207061767578125,0.052337646484375,-0.004245758056640625,0.04864501953125,0.0146484375,0.05718994140625,-0.01105499267578125,0.020660400390625,-0.0141754150390625,0.048248291015625,-0.044036865234375,0.0196685791015625,-0.0120391845703125,0.0082550048828125,0.01031494140625,0.020599365234375,0.0198211669921875,0.021331787109375,-0.0211029052734375,0.027679443359375,-0.0019426345825195312,0.0173187255859375,0.004001617431640625,-0.068603515625,-0.00823211669921875,-0.00957489013671875,0.059722900390625,0.007549285888671875,-0.0120391845703125,0.086181640625,-0.03289794921875,0.010986328125,-0.021697998046875,-0.084228515625,-0.05377197265625,0.081787109375,0.01377105712890625,0.01006317138671875,-0.044403076171875,-0.045379638671875,0.029083251953125,-0.055206298828125,-0.0205078125,-0.046600341796875,0.065673828125,0.08233642578125,0.0302276611328125,-0.01033782958984375,0.007793426513671875,0.06170654296875,-0.01270294189453125,-0.01256561279296875,0.07794189453125,-0.010528564453125,-0.01049041748046875,0.025848388671875,-0.087158203125,-0.00937652587890625,-0.03289794921875,0.0038127899169921875,0.07275390625,0.054290771484375,-0.026885986328125,-0.00829315185546875,-0.042633056640625,0.0025577545166015625,-0.0017404556274414062,0.0302886962890625,-0.0264434814453125,0.01045989990234375,-0.039154052734375,0.0299530029296875,-0.0036773681640625,-0.071533203125,0.0008149147033691406,0.063720703125,-0.042327880859375,0.01448822021484375,-0.0246734619140625,-0.06878662109375,-0.002063751220703125,0.0078582763671875,0.0012798309326171875,0.01201629638671875,-0.01220703125,0.0279541015625,0.06134033203125,0.07977294921875,-0.0794677734375,-0.004276275634765625,-0.01059722900390625,-0.0411376953125,0.03948974609375,-0.0259246826171875,0.09283447265625,-0.067626953125,-0.052490234375,0.01441192626953125,-0.03497314453125,-0.048858642578125,-0.11151123046875,-0.0335693359375,0.0606689453125,0.0148468017578125,-0.00479888916015625,0.0007290840148925781,0.034027099609375,0.023834228515625,-0.01861572265625,-0.00646209716796875,0.0027523040771484375,0.032073974609375,-0.09417724609375,-0.0217742919921875,-0.0283660888671875,-0.01605224609375,0.0113983154296875,0.1824951171875,0.0107879638671875,-0.019866943359375,0.027679443359375,-0.0411376953125,0.005558013916015625,-0.048614501953125,-0.0231781005859375,-0.00989532470703125,-0.0123138427734375,0.0149383544921875,0.0266571044921875,0.024078369140625,0.0005826950073242188,-0.06280517578125,0.0181121826171875,-0.002910614013671875,-0.0167083740234375,-0.024993896484375,-0.0137939453125,-0.0289154052734375,0.047119140625,-0.025054931640625,-0.006130218505859375,0.0233306884765625,-0.0297698974609375,-0.03363037109375,0.0004024505615234375,-0.0301971435546875,-0.025360107421875,-0.033111572265625,-0.05364990234375,-0.03509521484375,0.0079345703125,0.0298004150390625,-0.004131317138671875,0.031341552734375,0.0289764404296875,0.026702880859375,0.05780029296875,-0.024078369140625,0.005283355712890625,-0.066162109375,0.04840087890625,0.078369140625,0.01142120361328125,-0.004451751708984375,0.044189453125,0.0200347900390625,0.006839752197265625,0.0247955322265625,0.014617919921875,0.003009796142578125,-0.0012531280517578125,0.0194244384765625,-0.034332275390625,-0.06524658203125,0.05279541015625,0.033721923828125,0.039886474609375,0.006412506103515625,-0.0239410400390625,-0.012237548828125,0.050018310546875,-0.057861328125,0.047882080078125,-0.0260772705078125,-0.05377197265625,-0.046966552734375,-0.0280303955078125,-0.041778564453125,0.0249786376953125,-0.005756378173828125,-0.01372528076171875,-0.09185791015625,0.02880859375,0.0198516845703125,-0.0295562744140625,0.0190277099609375,-0.0360107421875,0.055267333984375,-0.0477294921875,-0.076171875,-0.024200439453125,0.035430908203125,0.00417327880859375,-0.0035495758056640625,-0.0599365234375,-0.035400390625,0.004192352294921875,0.0244598388671875,0.026702880859375,0.02545166015625,0.00826263427734375,0.015869140625,-0.075927734375,0.07330322265625,-0.02362060546875,0.01403045654296875,-0.03326416015625,-0.027984619140625,0.06036376953125,0.026092529296875,0.07147216796875,-0.02117919921875,0.043975830078125,0.0131378173828125,0.054168701171875,-0.041259765625,-0.0126953125,0.040740966796875,0.05999755859375,-0.01241302490234375,-0.041534423828125,-0.04473876953125,0.0020465850830078125,-0.0258636474609375,-0.001049041748046875,-0.0169219970703125,-0.03759765625,0.029266357421875,-0.024261474609375,0.036529541015625,0.041168212890625,-0.038482666015625,-0.0032596588134765625,0.07305908203125,-0.05352783203125,0.055755615234375,0.0156707763671875,-0.009429931640625,0.032684326171875,-0.025177001953125,0.01256561279296875,0.0902099609375,0.0992431640625,-0.0174713134765625,-0.00716400146484375,-0.00629425048828125,-0.0204010009765625,-0.090087890625,-0.02880859375,0.01800537109375,-0.030303955078125,0.040496826171875,0.004913330078125,-0.04498291015625,-0.005641937255859375,0.0216064453125,0.11688232421875,-0.0084686279296875,-0.09259033203125,-0.046966552734375,-0.00827789306640625,0.056365966796875,0.0379638671875,-0.010162353515625,-0.056610107421875,-0.006305694580078125,-0.0167694091796875,0.0731201171875,-0.03436279296875,0.04302978515625,0.05078125,0.00962066650390625,-0.01922607421875,0.0225830078125,0.0167388916015625,-0.002910614013671875,-0.042266845703125,0.01123046875,0.0290374755859375,-0.01137542724609375,-0.023529052734375,0.0821533203125,-0.04290771484375,0.08074951171875,0.0003943443298339844,0.0218353271484375,0.0165557861328125,-0.038848876953125,-0.0240478515625,0.062744140625,0.01499176025390625,-0.0030841827392578125,0.03338623046875,0.0237274169921875,-0.07220458984375,0.0221099853515625,0.004241943359375,-0.019866943359375,0.018890380859375,0.0120391845703125,0.0255584716796875,-0.02716064453125,0.0234527587890625,0.09869384765625,0.0156707763671875,0.0018520355224609375,-0.04486083984375,-0.0009112358093261719,-0.023895263671875,0.006744384765625,0.0421142578125,0.0009751319885253906,-0.00916290283203125,-0.0179443359375,-0.015655517578125,-0.004756927490234375,0.02947998046875,-0.0157012939453125,-0.033843994140625,-0.06597900390625,-0.017059326171875,0.03326416015625,-0.03436279296875,0.004993438720703125,0.004909515380859375,-0.0121612548828125,-0.0166015625,0.03436279296875,0.08599853515625,-0.01239013671875,0.045654296875,0.0182647705078125,-0.0145263671875,-0.01454925537109375,0.12371826171875,-0.0200653076171875,0.0037517547607421875,-0.046173095703125,-0.053009033203125,0.01522064208984375,0.07281494140625,-0.0046539306640625,0.026123046875,-0.0257415771484375,-0.0174407958984375,0.02496337890625,-0.08990478515625,-0.02398681640625,-0.07366943359375,-0.0006928443908691406,-0.026123046875,-0.0467529296875,-0.00948333740234375,-0.00968170166015625,-0.005199432373046875,-0.006343841552734375,0.0665283203125,-0.047149658203125,-0.037384033203125,-0.095703125,0.0298004150390625,0.01137542724609375,-0.051300048828125,-0.018585205078125,0.00933074951171875,0.026641845703125,-0.0272064208984375,-0.0601806640625,-0.016265869140625,0.0298309326171875,-0.007411956787109375,0.03857421875,0.04473876953125,0.046142578125,-0.0122833251953125,-0.04132080078125,0.0947265625,-0.04644775390625,0.0139312744140625,0.051971435546875,-0.00021028518676757812,-0.00450897216796875,-0.07122802734375,-0.0167388916015625,0.06585693359375,-0.0048370361328125,-0.0232391357421875,-0.033050537109375,0.0260162353515625,0.0013494491577148438,-0.006282806396484375,-0.03387451171875,-0.0033740997314453125,0.0009613037109375,-0.02777099609375,-0.003681182861328125,0.0229034423828125,-0.0035457611083984375,-0.027191162109375,-0.001247406005859375,0.01039886474609375,0.0186767578125,0.041778564453125,0.0207366943359375,0.12469482421875,0.0084991455078125,-0.061187744140625,-0.008087158203125,-0.0009169578552246094,-0.054534912109375,0.00316619873046875,-0.051849365234375,-0.04547119140625,0.046051025390625,-0.0165557861328125,-0.027923583984375,-0.01178741455078125,0.0281219482421875,-0.01092529296875,0.052825927734375,-0.0401611328125,-0.018280029296875,0.0447998046875,0.0341796875,-0.01271820068359375,0.041595458984375,-0.00449371337890625,0.027801513671875,-0.03668212890625,0.038818359375,0.05303955078125,-0.028289794921875,0.0479736328125,-0.0192108154296875,0.01331329345703125,0.01372528076171875,0.029327392578125,-0.033111572265625,-0.00835418701171875,0.0168304443359375,0.003765106201171875,0.048980712890625,0.041778564453125,0.00949859619140625,-0.08612060546875,-0.034210205078125,-0.06573486328125,-0.0340576171875,-0.0216064453125,0.019287109375,-0.112060546875,-0.0200042724609375,-0.01360321044921875,0.00970458984375,0.0035800933837890625,0.062744140625,-0.0152435302734375,-0.024871826171875,-0.0330810546875,0.004749298095703125,-0.0294189453125,-0.07647705078125,-0.0202789306640625,-0.061492919921875,0.01383209228515625,0.050140380859375,-0.0513916015625,-0.05206298828125,-0.042694091796875,0.0626220703125,0.0283660888671875,0.0694580078125,0.026458740234375,0.000640869140625,-0.054046630859375,-0.03558349609375,-0.0202789306640625,0.00527191162109375,-0.04998779296875,0.0738525390625,-0.047027587890625,-0.043792724609375,0.0302276611328125,0.0122528076171875,-0.0034275054931640625,0.0360107421875,0.033111572265625,-0.021270751953125,-0.0213775634765625,0.090576171875,-0.032257080078125,0.029296875,0.01522064208984375,0.04644775390625,0.021148681640625,0.061676025390625,-0.11041259765625,0.005359649658203125,-0.03363037109375,0.0218048095703125,-0.01169586181640625,-0.056243896484375,-0.0777587890625,-0.046783447265625,-0.0178070068359375,-0.0109405517578125,0.0015020370483398438,0.055267333984375,0.0008668899536132812,-0.006008148193359375,-0.030364990234375,-0.0142364501953125,-0.0560302734375,-0.011627197265625,-0.038238525390625,-0.0180816650390625,0.037261962890625,-0.0018625259399414062,-0.09649658203125,0.08349609375,-0.04248046875,-0.0031414031982421875,-0.024658203125,-0.045867919921875,-0.005954742431640625,-0.0204925537109375,-0.042877197265625,-0.0081024169921875,0.0272216796875,-0.0166473388671875,0.00867462158203125,-0.05712890625,0.035247802734375,-0.0174407958984375,0.051361083984375,0.043121337890625,-0.013397216796875,0.007686614990234375,0.05181884765625,0.0316162109375,-0.029266357421875,-0.0010929107666015625,-0.014404296875,0.0391845703125,0.0204620361328125,-0.039947509765625,0.0187835693359375,-0.0265045166015625,-0.0158233642578125,-0.0104522705078125,-0.00302886962890625,-0.047149658203125,-0.01099395751953125,0.036529541015625,-0.0012578964233398438,0.08001708984375,-0.01531982421875,-0.03363037109375,0.015960693359375,-0.022125244140625,-0.00878143310546875,-0.0164337158203125]') < 0.6) and this is the output of
If I'm reading this correctly, 10 rows are being returned and 168 are removed, and the distance query is being run on |
This might be the solution: https://www.postgresql.org/docs/current/queries-with.html Will try this out and share updates. |
Hello, I was hoping to get some advice on how to best go about scoping Django querysets that hold vector data to ensure that the
CosineDistance
calculation is properly scoped to the relevant rows only. Here's how I'm currently querying the Django ORM:Python:
Which, on calling
print(similar_faces_qset.query)
, shows that the following SQL code has been generated:The
WHERE
clause here, specifically theAND ( "vsf_item"."vector" <=> [-0.734, 0.1616 ... ]) < 0.8 )
suggests to me that the vector calculation might be happening across all rows of the"vsf_item"
table. Is that accurate? If so, do you have any recommendations on how to best scope computation given the above queries? (I'm doing scoping similar in spirit to the above in different parts of my project, so was hoping for general guidelines re. best practices).Thanks!
Adjacent issue that's worth mentioning: pgvector/pgvector#203 (comment)
The text was updated successfully, but these errors were encountered: